Declaring and Using Cursor Variables in SQL Server


How To Declare and Use Cursor Variables in SQL Server Transact-SQL?



There are two ways to representing a cursor:

1. A cursor name - A static name representing a cursor object. A cursor name should be linked to a cursor object in the DECLARE statement.

2. A cursor variable name - A variable name pointing to a cursor object. A cursor variable name should be declared with the CURSOR data type. It should be then assigned with a cursor object using the SET statement.

The tutorial exercise below shows you how to declare a cursor variable and assign a cursor object to it:

USE FyiCenterData;

-- declare a cursor variable
DECLARE @fyi_cursor CURSOR;

-- assign a cursor object
SET @fyi_cursor = CURSOR FOR
  SELECT id, url, notes, counts, time FROM fyi_links;

-- execute the query and fetch results
OPEN @fyi_cursor;
DECLARE @id INT, @url VARCHAR(80), @notes VARCHAR(80),
  @counts INT, @time DATETIME;
FETCH NEXT FROM @fyi_cursor INTO @id, @url, @notes, 
  @counts, @time;
  FETCH NEXT FROM @fyi_cursor INTO @id, @url, @notes, 
    @counts, @time;
CLOSE @fyi_cursor;
DEALLOCATE @fyi_cursor;

101 NULL                0   Jan  1 2007
102 Nice site.          8   Jan  1 2007
1101 moc.retneciyf.ved NULL                0   Jan  1 2007
202     It's another search 0   Jan  1 2007
2101 NULL                0   Jan  1 2007
2102 NULL                0   Jan  1 2007
301      Added long time ago!0   Jan  1 1999
302       Added today!        0   Jul  1 2007


SCROLL - Creating Cursors for Backward Scrolling in SQL Server

"@@FETCH_STATUS" - Looping through Result Set in SQL Server

Using Cursors in SQL Server Transact-SQL

⇑⇑ SQL Server Transact-SQL Tutorials

2016-10-17, 1379🔥, 0💬