Home > Datatypes, SQL Tips > SQL Server CURSOR Life Cycle

SQL Server CURSOR Life Cycle


A simple Cursor life cycle with minimum definition

USE [AdventureWorks]
GO

-- Returns -3
SELECT CURSOR_STATUS('global','myCursor') AS 'Before Declare'

DECLARE myCursor CURSOR
FOR SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID BETWEEN 100 and 102

-- Returns -1
SELECT CURSOR_STATUS('global','myCursor') AS 'After Declare'

DECLARE @ContactID INT, @FirstName VARCHAR(50), @LastName VARCHAR(50)

OPEN  myCursor

-- Returns 1
SELECT CURSOR_STATUS('global','myCursor') AS 'Open Cusrsor'

FETCH NEXT FROM myCursor INTO @ContactID, @FirstName, @LastName
WHILE @@fetch_status=0
BEGIN

	-- SQL Statements with logic inside
	SELECT @ContactID, @FirstName, @LastName

	FETCH NEXT FROM myCursor INTO @ContactID, @FirstName, @LastName
END

-- Returns 1
SELECT CURSOR_STATUS('global','myCursor') AS 'While loop exited, all rows iterated'

CLOSE myCursor

-- Returns -1
SELECT CURSOR_STATUS('global','myCursor') AS 'Cursor is Closed'

DEALLOCATE myCursor

-- Returns -3
SELECT CURSOR_STATUS('global','myCursor') AS 'Cursor Deallocated'

More on Cursor functions on MS BOL: http://msdn.microsoft.com/en-us/library/ms186285(v=SQL.90).aspx

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 379 other followers

%d bloggers like this: