How to pass a list of ids to a stored procedure and iterate over them using a cursor

As you probably know there is no array type in T-SQL. Let’s say the end user selects some records and you want to pass the ids of each of these records to a stored procedure. You can just use Dynamic SQL to handle this, but what if you actually want to use a cursor to iterate over each one and do something for each item. There are many reasons such as calling a stored proc for each item or maybe sending mail, etc. In general I don’t like cursors because they are slow, but I think there are cases. I had one of those cases, and the number of records I needed to loop through was small so I have no problem doing so.

The idea is to just pass the list of ids as comma separated values as a string as a parameter to a stored procedure. Be careful how this list is generated. You never want to leave this data unchecked for SQL Injection attacks. In particular, I recommend building an array of integers (not strings) in .NET and then converting the array to a comma separated list of ids. I got the idea from here. I also has a wealth of other techniques for simulating arrays in T-SQL.

The problem you will soon run into is that you can’t use standard Cursor syntax when you use Dynamic SQL. The code below shows you how you can do so using a Cursor Variable. You can also do it using a Global Cursor. If you want to go the Global Cursor route, check out the code here.

The code below basically is called something like this:

IterateThroughItems ‘1234,456,789’

This would select three records with ids 1234, 456, 789 and then print out the ID. Instead of printing out the ID, you could print the FName, LName, sendmail, update other tables, whatever you like.

-- WARNING: The end user should NEVER be able to enter or submit any information that is then passed into
-- the @IDs parameter. If they do, SQL-injection is available for them to hijack the database.
-- Only pass data that has been type-checked, etc. For example, I recommend using an array or list of 
-- integers in .NET and then using string.join() to populate the value of @IDs.
create proc IterateThroughItems @IDs as varchar(2000)
as

Declare @SelectStmt as nvarchar(3000)
Declare @Sql as nvarchar(3200)

Declare @ID as int
Declare @FName as nvarchar(50)
Declare @LName as nvarchar(100)

-- this is a cursor variable and is needed so we can use dynamic-sql which we need to deal with the list of ids
Declare @PersonCursor CURSOR

Set @SelectStmt = 'select ID, FName, LName from Person where ID in (' + @IDs + ') order by ID'

Set @Sql = 'Set @PersonCursor = CURSOR FAST_FORWARD FOR ' + @SelectStmt + '; OPEN @PersonCursor'

exec sp_executesql @Sql, N'@PersonCursor CURSOR OUTPUT', @PersonCursor OUTPUT

FETCH NEXT FROM @PersonCursor into @ID, @FName, @LName

WHILE @@FETCH_STATUS = 0
BEGIN
    print 'Processing: ' + Cast(@ID as varchar(20))
    
    FETCH NEXT FROM @PersonCursor into @ID, @FName, @LName
END

CLOSE @PersonCursor
DEALLOCATE @PersonCursor