SQL Cursors

SQL cursors can be used to process multiple rows in one query, one row at a time.

Sample SQL query

-- Disable the message of no. of rows affected in output window
SET NOCOUNT ON

-- Declare variables to store a value in a column related to one row at a time
DECLARE @topicID char(11)
DECLARE @topic char(11)

-- Declare the statement to get result set (a select statement)
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT Topic_PK, TopicTitle
FROM Topic
WHERE Topic_PK < 50

-- Execute the select statement and populate the result set
OPEN c1

-- Get a row from the result set (column values defined in select statement in to the declared variables)
FETCH NEXT FROM c1
INTO @topicID, @topic

-- If there are no more rows to return
WHILE @@FETCH_STATUS = 0

BEGIN

PRINT 'Topic = ' + @topicID + @topic
FETCH NEXT FROM c1
INTO @topicID, @topic

END

-- Release resources
CLOSE c1
DEALLOCATE c1

How do you create a procedure with a cursor output parameter?

CREATE PROCEDURE OpenCrsr
(
         @OutCrsr CURSOR VARYING OUTPUT
)
AS...

To be continued ...

How do you use a loop instead of using a cursor?

Why you need to do that?