Using a cursor with dynamic sql in a stored proc

Question

I have dynamic sql statement I've created in a stored proc. I need to iterate over the results using a cursor. I'm having a hard time figuring out the right syntax. Here's what I'm doing

SELECT @SQLStatement = 'SELECT userId FROM users'

DECLARE @UserId

DECLARE users_cursor CURSOR FOR
EXECUTE @SQLStatment --Fails here. Doesn''t like this


OPEN users_cursor
FETCH NEXT FROM users_cursor
INTO @UserId

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC asp_DoSomethingStoredProc @UserId

END
CLOSE users_cursor
DEALLOCATE users_cursor

What's the right way to do this?

Answer 1

A cursor will only accept a Select statement, so if the SQL really needs to be dynamic make the declare cursor part of the statement you are executing. For the below to work your server will have to be using global cursors

Declare @UserID varchar(100)
declare @sqlstatement nvarchar(4000) 
--move declare cursor into sql to be executed
set @sqlstatement = 'Declare  users_cursor CURSOR FOR SELECT userId FROM users'

exec sp_executesql @sqlstatement 


OPEN users_cursor
FETCH NEXT FROM users_cursor
INTO @UserId

WHILE @@FETCH_STATUS = 0
BEGIN
Print @UserID
EXEC asp_DoSomethingStoredProc @UserId

FETCH NEXT FROM users_cursor --have to fetch again within loop 
INTO @UserId

END
CLOSE users_cursor
DEALLOCATE users_cursor

If you need to avoid using the global cursors, you could also insert the results of your dynamic sql into a temp table, and then use that table to populate your cursor.

Declare @UserID varchar(100)
create table #users (UserID varchar(100))

declare @sqlstatement nvarchar(4000) 
set @sqlstatement = 'Insert into #users (userID) SELECT userId FROM users'
exec(@sqlstatement)


declare users_cursor cursor for Select UserId from #Users
OPEN users_cursor
FETCH NEXT FROM users_cursor
INTO @UserId

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC asp_DoSomethingStoredProc @UserId

FETCH NEXT FROM users_cursor
INTO @UserId

END
CLOSE users_cursor
DEALLOCATE users_cursor

drop table #users

Answer 2

First off, avoid using a cursor if at all possible. Here are some resources for rooting it out when it seems you can't do without:

There Must Be 15 Ways To Lose Your Cursors... part 1, Introduction

Row-By-Row Processing Without Cursor

That said, though, you may be stuck with one after all--I don't know enough from your question to be sure that either of those apply. If that's the case, you've got a different problem--the select statement for your cursor must be an actual SELECT statement, not an EXECUTE statement. You're stuck.

But see the answer from cmsjr (which came in while I was writing) about using a temp table. I'd avoid global cursors even more than "plain" ones....

Answer 3

Old thread, but I just referenced it. Working with a non-relational database (IDMS anyone?) over an ODBC connection qualifies as one of those times where cursors and dynamic SQL seems the only route.

select * from a where a=1 and b in (1,2)

takes 45 minutes to respond

while re-written to use keysets without the in clause will run in under 1 second:

Select * from a where (a=1 and b=1) union all select * from a where (a=1 and b=2)

if the in statement for column B contains 1145 rows, using a cursor to create indidivudal statements and execute them as dynamic SQL is far faster than using the in clause. Silly hey?

And yes, there's no time in a relational databse that cursor's should be used...I just can't beleive I've come across an instance where a cursor loop is several magnitudes quicker.