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?
This question is related to
tsql
stored-procedures
dynamic-sql
database-cursor
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
After recently switching from Oracle to SQL Server (employer preference), I notice cursor support in SQL Server is lagging. Cursors are not always evil, sometimes required, sometimes much faster, and sometimes cleaner than trying to tune a complex query by re-arranging or adding optimization hints. The "cursors are evil" opinion is much more prominent in the SQL Server community.
So I guess this answer is to switch to Oracle or give MS a clue.
for
loop implicitly defines/opens/closes the cursor!)Another option in SQL Server is to do all of your dynamic querying into table variable in a stored proc, then use a cursor to query and process that. As to the dreaded cursor debate :), I have seen studies that show that in some situations, a cursor can actually be faster if properly set up. I use them myself when the required query is too complex, or just not humanly (for me ;) ) possible.
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.
this code can be useful for you.
example of cursor use in sql server
DECLARE sampleCursor CURSOR FOR
SELECT K.Id FROM TableA K WHERE ....;
OPEN sampleCursor
FETCH NEXT FROM sampleCursor INTO @Id
WHILE @@FETCH_STATUS <> -1
BEGIN
UPDATE TableB
SET
...
There is another example which I would like to share with you
:D
http://www.sommarskog.se/dynamic_sql.html#cursor0
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....
Source: Stackoverflow.com