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?

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

程式碼工作室  吳 Sir
Skype: wu_chung_pin

Line: paulwu0114

亞太:0977387198

電話:03-5509853

傳真:03-5509853

官方網址: http://www.runcode.com.tw