SQL 2005 Cursor Template

SQL 2005 cursor template

SQL Server 2005 cursor example

SQL SERVER 2005 TSQL Cursors

Once in a while, we might have to use a cursor in our stored procedure. Normally, I will keep the template in my folder and retrieve it whenever I need to consume it. It saves me some time trying to recollect the complete syntax. Here is the template with a brief example, hope someone will find it useful.

SET NOCOUNT ON
GO

--sample table
IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL
 BEGIN
	DROP TABLE #MyTempTable
    CREATE TABLE #MyTempTable (
		column1 varchar(10),
		column2 varchar(10),
		column3 varchar(10)
	)
 END
--Insert dummy data
INSERT INTO #MyTempTable (column1, column2, column3) values('apple', 'apple2', 'apple3')
INSERT INTO #MyTempTable (column1, column2, column3) values('banana', 'banana2', 'banana3')
INSERT INTO #MyTempTable (column1, column2, column3) values('grape', 'grape2', 'grape3')

--Template start here
DECLARE @Column1 varchar(10), @Column2 varchar(10)

DECLARE db_cursor CURSOR FOR 
SELECT column1, column2 FROM #MyTempTable

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @Column1, @Column2

WHILE @@FETCH_STATUS = 0  
BEGIN  
	
	  --do work here	
      print @Column1 + ' ' + @Column2

	FETCH NEXT FROM db_cursor INTO @Column1, @Column2
END  

CLOSE db_cursor  
DEALLOCATE db_cursor
--End template