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

User · Answer

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  lt  gt  -1 BEGIN  UPDATE TableB    SET

User · Answer

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

User · Answer

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

User · Answer

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    Oracle EXECUTE IMMEDIATE into a cursor       Loop through an implicit cursor  a for loop implicitly defines opens closes the cursor

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

There is another example which I would like to share with you   D http   www sommarskog se dynamic sql html cursor0

[tsql] Using a cursor with dynamic SQL in a stored procedure

Examples related to tsql

Examples related to stored-procedures

Examples related to dynamic-sql

Examples related to database-cursor