Using a cursor with dynamic SQL in a stored procedure

Question

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?

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

Answer 2

This code is a very good example for a dynamic column with a cursor, since you cannot use '+' in @STATEMENT:

ALTER PROCEDURE dbo.spTEST AS     SET NOCOUNT ON     DECLARE @query NVARCHAR(4000) = N'' --DATA FILTER     DECLARE @inputList NVARCHAR(4000) = ''     DECLARE @field sysname = '' --COLUMN NAME     DECLARE @my_cur CURSOR     EXECUTE SP_EXECUTESQL         N'SET @my_cur = CURSOR FAST_FORWARD FOR             SELECT                 CASE @field                     WHEN ''fn'' then fn                     WHEN ''n_family_name'' then n_family_name                 END             FROM                 dbo.vCard             WHERE                 CASE @field                     WHEN ''fn'' then fn                     WHEN ''n_family_name'' then n_family_name                 END                 LIKE ''%''+@query+''%'';             OPEN @my_cur;',         N'@field sysname, @query NVARCHAR(4000), @my_cur CURSOR OUTPUT',         @field = @field,         @query = @query,         @my_cur = @my_cur OUTPUT     FETCH NEXT FROM @my_cur INTO @inputList     WHILE @@FETCH_STATUS = 0     BEGIN         PRINT @inputList         FETCH NEXT FROM @my_cur INTO @inputList     END     RETURN