Using Cursors Examples in T Sql

Ex 1.

Declare @FName as nvarchar(50)
Declare @LName as nvarchar(100)
Declare @Email as nvarchar(255)

Declare PersonCursor CURSOR FAST_FORWARD FOR

Select fname, lname, email from Person
where city = 'Phoenix'
order by fname

OPEN PersonCursor
FETCH NEXT FROM PersonCursor
INTO @FName, @LName, @Email

WHILE @@FETCH_STATUS = 0
BEGIN
-- do row specific stuff here
print 'FName: ' + @FName
print 'LName: ' + @LName
print 'Email: ' + @Email

FETCH NEXT FROM PersonCursor
INTO @FName, @LName, @Email
END

CLOSE PersonCursor
DEALLOCATE PersonCursor


About @@FETCH_STATUS
0 The FETCH statement was successful.

-1 The FETCH statement failed or the row was beyond the result set.

-2 The row fetched is missing.


Ex 2.

DECLARE @price money
DECLARE @get_price CURSOR

SET @get_price = CURSOR FOR
SELECT price FROM PriceTable where SysID<100

OPEN @get_price

FETCH NEXT FROM @get_price INTO @price

WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @Price < 20
SELECT 'Under 20'
ELSE
SELECT @Price

FETCH NEXT FROM @get_price INTO @price
END

CLOSE @get_price
DEALLOCATE @get_price

As cursors are very poor performers and often other options make more sense for performance reasons, we should be caution when using cursors.