by bryian
2. August 2009 15:45
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
Subscribe