How to use MSSQL Server cursors?
SQL Server cursors are database objects used to manipulate data in a set on a row-by-row basis. You can fetch cursor rows and perform operations on them in a loop just like using any looping mechanism found in any other programming language. Before you can use a cursor, you need to declare it.
Example:
declare @row as varchar(20)
DECLARE cur1 CURSOR FOR
SELECT rowid FROM Table1
OPEN cur1
FETCH NEXT FROM cur1 INTO @row WHILE @@FETCH_STATUS = 0 BEGIN
SELECT * FROM Table1 WHERE rowid = @row
FETCH NEXT FROM cur1 INTO @row
END CLOSE cur1 DEALLOCATE cur1
DECLARE CURSOR
The DECLARE CURSOR command defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates. DECLARE CURSOR accepts both a syntax based on the SQL-92 standard and a syntax using a set of Transact-SQL extensions.
Filed under: MSSQL Server cursors | Tagged: Author Raghu, cursors, Example of cursors, how to user cursors, MSSQL Server cursors, Select row by row, SQL cursor | Leave a comment »