Search code examples
t-sqlstored-proceduresdynamic-sqldatabase-cursor

Using a cursor with dynamic SQL in a stored procedure


I have a dynamic SQL statement I've created in a stored procedure. 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?


Solution

  • 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 temporary 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