Declare cursor based on dynamic query

Declare cursor based on dynamic query

• etcheverrjc

  You can use dynamic SQL to create a global cursor as shown in another reply in this thread. But what are you trying to do Why do you need to use a cursor And why do you need to use dynamic SQL Both have performance implications. And dynamic SQL has serious security implications that can compromise your database system and/or network. You will have to use techniques (both in the database and client-side depending on how you call your SP) that avoid SQL injection to protect your database and network from malicious users. Apart from these problems, dynamic SQL requires more maintainence because you have to grant more permissions to end users since checks are deferred to run-time unlike SPs with static SQL statements. So it is easy to create a cursor dynamically but that is not the right thing to do in majority of the cases.
  
  

• prrao

  this is not possible. i agree with waaz
  
  

• Horst Klein

  Instead of local cursor, you can create a Global cursor with dynamic sql, which is available beyond the scope the dynamic sql

  like this

  set @sql='declare test cursor global for '+ @tempsql

  exec sp_executesql @sql

  open test

  close test

  
  

• JimBobJoe

  hey whitney,

  i got the same problem of dynamic query with cursors..

  You gave the alternative but i got the big cursor and its difficult for me to put the entire stuff in string.

  Because it gets difficult to maintain for me.

  Any help or comment regarding this will be appreciated.

  Thanks a ton!!

  dromyl@hotmail.com

  
  

• AshMetry

  Also, try not to ask the same question twice.  This question was also answered in another thread.  I  have merged the threads into one.

  
  
  

• Robert S P

  You could add the cursor creation to your dynamic sql and then just call sp_executesql for the built up string. Something like...

  DECLARE @sql nvarchar(4000)

  --Get beginning of cursor

  SELECT @sql = 'DECLARE c CURSOR FOR'

  --Decision code for what query is built

  SELECT @sql = @sql + 'SELECT * FROM orders'

  --Remainder of cursor with specific columns from above query

  SELECT @sql = @sql + 'OPEN c FETCH NEXT FROM c INTO ....'

  --Execute the string we just built

  EXEC sp_executesql @sql

  
  

• ryan101

  Hi

  I am writing the code as below

  Declare @testSQl varchar(1000)

  set @testsql = 'select * from orders'

  declare test1 cursor for @testSQl

  The declare statement is not working . My @testsql will be generated at run time.

  Help

  Nitin

  
  

• Bravo2007

  You can not use dynamic sql while opening the cursors..
  
  it should be like this
  
  Declare Test1 cursor for
  Select * From Orders
  
  

• twaltz

  Hi,

  I dont know for the moment how to declare a cursor on a query from a string.. I dont think its possible this way. An alternative is to find a solution other than using the cursor, else you'd lose development time in trying to find a solution.

  If you cannot find a solution, try to explain the problem, someone will try help out, and also cursors generally tend to be less performant.

  
  
  

• Nilesh Meshram

  I don't like to ever advocate the use of cursors, but you can do this using a global cursor, if you really must:

  create procedure test
  as
  declare @name nvarchar(128)
  exec ('declare bob cursor global for select name from sys.objects')
  open bob

  fetch next from bob into @name
  select @name as works
  close bob
  deallocate bob
  go

  test

  
  
  
• Declare cursor based on dynamic query