Using a cursor with dynamic SQL in a stored procedure
Solution 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
Solution 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
Solution 3
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 database that cursor's should be used. I just can't believe I've come across an instance where a cursor loop is several magnitudes quicker.
Solution 4
There is another example which I would like to share with you
:D
http://www.sommarskog.se/dynamic_sql.html#cursor0
Solution 5
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....
![Micah](https://i.stack.imgur.com/T1WbE.png?s=256&g=1)
Micah
Updated on May 10, 2020Comments
-
Micah about 4 years
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?
-
marc_s about 15 yearsavoid cursors - they're EVIL ! :-)
-
cmsjr about 15 yearsCursors are not evil. Resource intensive and inadvisable for use in production scenarios, sure. Evil no. Just because something can be used incorrectly does not mean that you should not know how to use it. Maybe I misread the question, but it seemed to be asking about how to use a cursor, not the pros and cons for their usage.
-
RolandTumble about 15 yearsI'm going with evil.... Yes, the OP asked about how to use a cursor. He also asked about "...the right way to do this." The responsible answer is to give information on how to accomplish the end result without a cursor--it's (almost) always possible to do so.
-
quillbreaker over 13 yearsThis gives me the power to write code that is completely incomprehensible! Variables declared in dynamically generated SQL used in non-dynaimic sql farther down the execution path! I love it!
-
tjmoore over 11 yearsJust an FYI - Both those articles require you to register to view now.
-
tjmoore over 11 yearsCursors and likewise dynamic SQL are a necessary evil at limited times but only where underlying database design has forced them to be used. Good design should never result in anything you can't do set based and should never involve dynamic schema.
-
Zar Shardan over 11 yearsBoth cursors and dynamic SQL should be used when they are the right tools for the job. For example cursors are suitable for the RunningTotal type of jobs where they provide linear performance as opposed to exponential for most (if not all) portable set-based versions.
-
Zar Shardan over 11 yearsThe 2nd example is really bad. He creates a stored procedure and a trigger just to avoid using cursors. I doubt that his approach is better/more performant than just a simple forward cursor.
-
Dale almost 11 yearsThis works for me, and avoids global cursors and temporary tables. Thanks
-
Ian Campbell over 9 yearsPerfect, very useful thanks!. As a note, it seems that the cursor must also be opened within the dynamic sql -- SQL Server seems to think the cursor is not initialized, and was throwing an error for me if done after the dynamic stuff.
-
Tsahi Asher about 9 yearsI can't see how concatenating
@query
into the sql is going to work here. I think you're going to getLIKE '%'xx'%';
, assuming@query
is set to'xx'
, which is invalid sql. and the docs for SP_EXECUTESQL state: "@stmt must be either a Unicode constant or a Unicode variable.", meaning you can't build the string and execute it immediately without a temporary variable. -
Cruachan about 9 yearsThere are very few code constructs that are intrinsically evil (I'm looking at you COBOL, rewritable code is evil), and cursors are not one of them. Sure beginners may be tempted to reach for them because they don't understand handling sets in SQL, but sometimes they are life saving - I've developed several SSRS reports you couldn't possibly handle any other way
-
Mr.J over 6 yearsI thank you from 2017! Had me digging around so much for this answer.
-
tone almost 6 yearsCursors are not always bad. Greg low wrote an article a few years back that demonstrated that cursors were great for preventing blocking when manipulating data in large tables. Do a large update, get blocking, use cursors, row locking only. Here's that article: aspalliance.com/articleViewer.aspx?aId=1184&pId=-1
-
Jimbo over 5 yearsFor 20 years I've prided myself on finding ways to avoid using cursors in situations where it intuitively seems like the best approach. But there are still cases where it is necessary. Right now I am tasked with building dynamic data migration scripts. My first attempt at inserting to the production table from staging involves no cursor. Just a set-based insert statement. But that whole thing will fail if any individual record cannot be inserted. So on failure I kick off a process of inserting the lines individual so we can load as much as possible. Tell me how to do that without a cursor
-
volkerk over 5 yearsIt is not correct that you need to rely on global cursors, as the answer by SMHMayboudi clearly shows. Therefore this one should not have so many upvotes - it is misinformation.
-
volkerk over 5 yearsWould be even more useful if it explained why the OPEN CURSOR statement needs to be inside the dynamic SQL, where one would not expect to have to put it.
-
cmsjr over 5 yearsI would say it is not misinformation, the code I supplied does required global cursors to be enabled, that is not the same as saying that it is necessary to use global cursors to solve this problem.
-
Subbu over 4 yearsThank you for referencing Erland Sommarskog. His posts are very insightful