MSSQL Server cursors

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.

Advertisements