Looking for suggestions to improve the following scripts:



I'm trying to make my job easier by generating code and then modifying it rather than writing it from scratch.

DROP PROC	sp_GenerateUpdateStatement
GO

CREATE PROC	sp_GenerateUpdateStatement
		@table		VARCHAR(100)

AS

DECLARE		@updatesql	VARCHAR(8000)
DECLARE		@valuessql	VARCHAR(8000)
DECLARE		@name		VARCHAR(100)
DECLARE		@ID		INT
DECLARE		@CRLF		CHAR(2)

SELECT		@CRLF = CHAR(13) + CHAR(10)

SELECT 		@updatesql = '		UPDATE 		' + @table + @crlf + '		SET' + @crlf

DECLARE 	mycursor INSENSITIVE CURSOR FOR
SELECT 		dbo.syscolumns.name
FROM		dbo.sysobjects,
		dbo.syscolumns,
		dbo.systypes
WHERE		dbo.syscolumns.xtype = dbo.systypes.xtype  
AND  		dbo.syscolumns.id = dbo.sysobjects.id
AND  		dbo.sysobjects.xtype = 'U'
AND		dbo.sysobjects.name = @table
AND  		dbo.sysobjects.xtype='U'
ORDER BY 	colorder

FOR READ ONLY

OPEN 		mycursor

FETCH NEXT FROM mycursor into @name
WHILE @@FETCH_STATUS = 0
BEGIN
		SELECT 		@updatesql = @updatesql + '				' + @name + 
		CASE
			WHEN LEN(@name) <= 7 THEN '				'
			WHEN LEN(@name) <= 15 THEN '			'
			WHEN LEN(@name) <= 16 THEN '		'
			WHEN LEN(@name) <= 22 THEN '		'
			ELSE '	'
		END
		+ ' = @' + @name + ', ' + @crlf
		FETCH NEXT FROM mycursor into @name
END

CLOSE 		mycursor
DEALLOCATE 	mycursor

SELECT		@updatesql = SUBSTRING(@updatesql, 1, LEN(@updatesql) - 4)

PRINT		@updatesql + @crlf + '		WHERE		1 = 1' + @crlf
GO

----------------

DROP PROC	sp_GenerateCursorCode
GO

CREATE PROC	sp_GenerateCursorCode
		@table		VARCHAR(100),
		@loopcommand	VARCHAR(8000) = '-- Do something here'

AS

DECLARE		@name		VARCHAR(100)
DECLARE		@sql		VARCHAR(8000)
DECLARE		@fetchnext	VARCHAR(8000)
DECLARE		@crlf		CHAR(2)

SELECT		@crlf 		= CHAR(13) + CHAR(10)

SELECT 		@sql 		= 'DECLARE ' + @table + '_cursor INSENSITIVE CURSOR FOR ' + @crlf + 'SELECT '
SELECT 		@fetchnext 	= 'FETCH NEXT FROM ' + @table + '_cursor INTO '

DECLARE 	mycursor INSENSITIVE CURSOR FOR
SELECT 		dbo.syscolumns.name
FROM		dbo.sysobjects,
		dbo.syscolumns,
		dbo.systypes
WHERE		dbo.syscolumns.xtype = dbo.systypes.xtype  
AND  		dbo.syscolumns.id = dbo.sysobjects.id
AND  		dbo.sysobjects.xtype = 'U'
AND		dbo.sysobjects.name = @table
AND  		dbo.sysobjects.xtype='U'
ORDER BY 	colorder
FOR READ ONLY

OPEN 		mycursor

FETCH NEXT FROM mycursor INTO @name
WHILE 		@@FETCH_STATUS = 0
BEGIN
		SELECT 		@sql 		= @sql + '		@' + @name + ', ' + @crlf
		SELECT 		@fetchnext 	= @fetchnext + '@' + @name + ', '
		FETCH NEXT FROM mycursor INTO @name
END

CLOSE 		mycursor
DEALLOCATE 	mycursor

SELECT		@sql = SUBSTRING(@sql, 1, LEN(@sql) - 4) + @crlf
SELECT		@fetchnext = SUBSTRING(@fetchnext, 1, LEN(@fetchnext) - 1) 
SELECT 		@sql = @sql + 'FROM 		' + @table + @crlf + 'FOR READ ONLY' + @crlf + @crlf + 'OPEN 		' + @table + '_cursor' + @crlf
SELECT		@sql = @sql + @crlf + @fetchnext + @crlf
SELECT		@sql = @sql + 'WHILE @@FETCH_STATUS = 0' + @crlf + 'BEGIN' + @crlf + '		' + @loopcommand + @crlf  
SELECT		@sql = @sql + '		' + @fetchnext + @crlf
SELECT		@sql = @sql +  'END' + @crlf + @crlf + 'CLOSE		' + @table + '_cursor' + @crlf + 'DEALLOCATE	' + @table + '_cursor' + @crlf

PRINT 		@sql + @crlf

---------------------------

DROP PROC	sp_GenerateInsertStatement
GO

CREATE PROC	sp_GenerateInsertStatement
		@table		VARCHAR(100)

AS

DECLARE		@insertsql	VARCHAR(8000)
DECLARE		@valuessql	VARCHAR(8000)
DECLARE		@name		VARCHAR(100)
DECLARE		@ID		INT
DECLARE		@CRLF		CHAR(2)

SELECT		@CRLF = CHAR(13) + CHAR(10)

SELECT 		@insertsql = '		INSERT 		' + @table + @crlf + '		(' + @crlf
SELECT 		@valuessql = '		VALUES ' + @crlf + '		(' + @crlf

DECLARE 	mycursor INSENSITIVE CURSOR FOR
SELECT 		dbo.syscolumns.name
FROM		dbo.sysobjects,
		dbo.syscolumns,
		dbo.systypes
WHERE		dbo.syscolumns.xtype = dbo.systypes.xtype  
AND  		dbo.syscolumns.id = dbo.sysobjects.id
AND  		dbo.sysobjects.xtype = 'U'
AND		dbo.sysobjects.name = @table
AND  		dbo.sysobjects.xtype='U'
ORDER BY 	colorder

FOR READ ONLY

OPEN 		mycursor

FETCH NEXT FROM mycursor into @name
WHILE @@FETCH_STATUS = 0
BEGIN
		SELECT 		@insertsql = @insertsql + '				' + @name + ', ' + @crlf
		SELECT 		@valuessql = @valuessql + '				@' + @name + ', ' + @crlf
		FETCH NEXT FROM mycursor into @name
END

CLOSE 		mycursor
DEALLOCATE 	mycursor

SELECT		@insertsql = SUBSTRING(@insertsql, 1, LEN(@insertsql) - 4)
SELECT		@valuessql = SUBSTRING(@valuessql, 1, LEN(@valuessql) - 4)
SELECT 		@insertsql = @insertsql + @crlf + '		)'
SELECT 		@valuessql = @valuessql + @crlf + '		)'

PRINT		@insertsql + @crlf + @valuessql

GO

--------------------------

DROP PROC	sp_GenerateVariableDeclarations
GO

CREATE PROC	sp_GenerateVariableDeclarations
			@table		VARCHAR(100)

AS

SET NOCOUNT ON

SELECT 		'DECLARE 	@' + dbo.syscolumns.name +
		CASE
			WHEN LEN(dbo.syscolumns.name) <= 6 THEN '				'
			WHEN LEN(dbo.syscolumns.name) <= 14 THEN '			'
			WHEN LEN(dbo.syscolumns.name) <= 16 THEN '		'
			WHEN LEN(dbo.syscolumns.name) <= 22 THEN '		'
			ELSE '	'
		END
		+
		UPPER(dbo.systypes.name) + 
		CASE
			WHEN PATINDEX('%char%',dbo.systypes.name) != 0 THEN '(' + cast(dbo.syscolumns.length AS VARCHAR(5)) + ')'
			ELSE ''
		END
FROM		dbo.sysobjects,
		dbo.syscolumns,
		dbo.systypes
WHERE		dbo.syscolumns.xtype = dbo.systypes.xtype  
AND  		dbo.syscolumns.id = dbo.sysobjects.id
AND  		dbo.sysobjects.xtype = 'U'
AND		dbo.sysobjects.name = @table
AND  		dbo.sysobjects.xtype='U'
ORDER BY 	colorder

GO

Any ideas?