Create T-SQL Cursor Code For any Table

				
--**************************************
-- Name: Create T-SQL Cursor Code For any Table
-- Description:Got tired of hand coding all that T-SQL cursor code so I created this to create the shell template for me.
Just choose any table you want to create a template for to process the records using a cursor.
-- By: Limbo
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=643&lngWId=5--for details.--**************************************

declare @Table_Name varchar(50)
set @Table_Name='INSERT TABLENAME HERE'
	if not exists (select * 
	from INFORMATION_SCHEMA.COLUMNS
	where TABLE_NAME = @Table_Name)
	begin
	 print 'Table name not found.'
	 goto ENDX
	end
	
	declare @Column_Name varchar(50)
	declare @Data_Type varchar(50)
	declare @Character_Maximum_Length int
	declare @Numeric_Precision int
	declare @Numeric_Scale int
		
	declare col cursor fast_forward for
	select COLUMN_NAME,
	 DATA_TYPE,
	 CHARACTER_MAXIMUM_LENGTH,
	 NUMERIC_PRECISION, 
 NUMERIC_SCALE from INFORMATION_SCHEMA.COLUMNS
 where TABLE_NAME = @Table_Name
 		
	
	/* ---------------------------------------------- */
	/* Spew field declarations for TSQL*/
	/* ---------------------------------------------- */
	
	open col
	
	print ' '
	print '/* Record definition for table: ' + upper(@Table_Name) + ' */'
	print ' '
	
	READLOOP1:
	fetch next from col into
			@Column_Name,
			@Data_Type,
			@Character_Maximum_Length, 
			@Numeric_Precision,
			@Numeric_Scale
	
if @@FETCH_STATUS = -1 goto EOF_READLOOP1 /* EOF */
if @@FETCH_STATUS = -2 goto READLOOP1 /* DELETED ROW */
	
	if (@Data_Type = 'char' or
	@Data_Type = 'varchar' or
	@Data_Type = 'nchar' or
	@Data_Type = 'nvarchar' or
	@Data_Type = 'text' or
	@Data_Type = 'ntext' or
	@Data_Type = 'binary' or
	@Data_Type = 'varbinary' or
	@Data_Type = 'image' or
	@Data_Type = 'uniqueidentifier')
	begin
	 print 
	 'declare @' + 
	 @Column_Name + 
	 ' ' + 
	 @Data_Type + 
	 '(' + 
	 ltrim(rtrim(str(@Character_Maximum_Length))) + 
	 ')'
	 goto READLOOP1
	end
	if (@Data_Type = 'datetime' or
	@Data_Type = 'timestamp' or
	@Data_Type = 'smalldatetime' or
	@Data_Type = 'int' or
	@Data_Type = 'smallint' or
	@Data_Type = 'tinyint')
	begin
	 print 
	 'declare @' + 
	 @Column_Name + 
	 ' ' + 
	 @Data_Type
	 goto READLOOP1
	end
	print 
	'declare @' + 
	@Column_Name + 
	' ' + 
	@Data_Type +
 	'(' + 
	ltrim(rtrim(str(@Numeric_Precision))) + 
	', ' +
 	ltrim(rtrim(str(@Numeric_Scale))) + 
	')'
	goto READLOOP1
		
	EOF_READLOOP1:
	close col
	
	/* --------------------------------------------- */
	/* Spew cursor declaration for for TSQL */
	/* --------------------------------------------- */
	
	open col
	
	print ' '
	print '/* Declare cursor for table: ' + upper(@Table_Name) + ' */'
	print ' '
	
	print 'declare CURSOR_' + upper(@Table_Name) + ' cursor LOCAL'
	print 'FAST_FORWARD for select' 
	 	
	READLOOP2:
	fetch next from col into
			@Column_Name,
			@Data_Type,
			@Character_Maximum_Length, 
			@Numeric_Precision,
			@Numeric_Scale
	
if @@FETCH_STATUS = -1 goto EOF_READLOOP2 /* EOF */
if @@FETCH_STATUS = -2 goto READLOOP2 /* DELETED ROW */
	
	print ' ' + @Column_Name + ','
		
	goto READLOOP2
		
	EOF_READLOOP2:
	close col
	print 'from ' + upper(@Table_Name)
	print 'where'
	print ' [colname] = @Variable, etc.'
	print 'for read only'
	print ' '
	print '/* Open Cursor */'
	print 'open CURSOR_' + upper(@Table_Name)
	
	/* --------------------------------------------- */
	/* Spew fetch-into declaration for for TSQL */
	/* --------------------------------------------- */
	
	open col
	
	print ' '
	print '/* Fetch for table: ' + upper(@Table_Name) + ' */'
	print ' '
	print 'READLOOPx:'
	print ' '
	print 'fetch next from CURSOR_' + upper(@Table_Name) + ' into'
	 	
	READLOOP3:
	fetch next from col into
			@Column_Name,
			@Data_Type,
			@Character_Maximum_Length, 
			@Numeric_Precision,
			@Numeric_Scale
	
if @@FETCH_STATUS = -1 goto EOF_READLOOP3 /* EOF */
if @@FETCH_STATUS = -2 goto READLOOP3 /* DELETED ROW */
	
	print ' @' + @Column_Name + ','
			
	goto READLOOP3
		
	EOF_READLOOP3:
	close col
	print ' '
	print 'if @@FETCH_STATUS = -1 goto EOF_READLOOPx /* END OF RECORD SET */'
print 'if @@FETCH_STATUS = -2 goto READLOOPx /* DELETED ROW, READ AGAIN */'
	print ' '
	print '/* Process record from table: ' + upper(@Table_Name) + ' */'
	print ' '
	print ' < record processing happens here >'
	print ' '
	print 'goto READLOOPx /* Get next record from table: ' + upper(@Table_Name) + ' */'
	print ' '
	print 'EOF_READLOOPx:'
	print ' close CURSOR_' + upper(@Table_Name)
	print ' deallocate CURSOR_' + upper(@Table_Name)
	
/* End of use for this cursor, so deallocate it */
		
	deallocate col
	
ENDX:
	return