SQL: Using a Cursor in a Loop


While SQL was designed for set operations and not loops, it does still support them. Given this, I rarely use cursors and loops when performing SQL operations and thus I always have to look up the syntax. I’m posting this here mainly for my benefit.

-- Note that we are only selecting one column here but a cursor just points 
-- to a row in a set so we could select multiple columns
DECLARE curEmployeeIds CURSOR FOR (SELECT Id FROM Employees)

-- Declare the variables that will be used in the loop that 
-- correspond to one record's values
DECLARE @Id int

OPEN curEmployeeIds

-- If you need multiple values from a single record, simply
-- enter a comma delimited list of variable names after INTO
FETCH NEXT FROM curEmployeeIds INTO @Id

WHILE @@FETCH_STATUS = 0
BEGIN

-- Perform the needed operations

FETCH NEXT FROM curEmployeeIds INTO @Id
END

-- Release the resources
CLOSE curEmployeeIds
DEALLOCATE curEmployeeIds
Posted in SQL. Tags: . 2 Comments »

2 Responses to “SQL: Using a Cursor in a Loop”

  1. Skyler Onken Says:

    Im no SQL Server guy, but in Oracle you can avoid the entire Declare statements by using a for loop and declaring the cursor within the IN statement.

    In that case it will automatically end at the end of the cursors results and can execute commands like any other cursor.

    • Nick Olsen Says:

      I have seen the Oracle syntax for this before and it does look quite a bit more elegant. I do not believe you can do this with MS SQL but I could be wrong.


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

%d bloggers like this: