Using Dynamic SQL in a Cursor

• dogramone

  Hall of Fame

  Points: 3216

  More actions

  January 9, 2003 at 6:35 pm

  #159319

  Hi all,

  I'm trying to generate the SQL in the fetch component of a cusror using variables.

  i.e.

  declare z_cursor for

  select storeid from @table

  when I try to run it I get "must declare @table". It has been declared and populated, there are no "go" statements between to clear the variable.

  Can anyone help with a way to use variables in the cursor declare step?

  Thanks

  Mark

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  January 10, 2003 at 3:44 am

  #446030

  Try

  set @sql = 'DECLARE z_cursor CURSOR FOR select storeid from ' + @table

  exec sp_executesql @sql

  OPEN z_cursor

  FETCH ....

  CLOSE curs

  DEALLOCATE curs

  Far away is close at hand in the images of elsewhere.
  Anon.

• dogramone

  Hall of Fame

  Points: 3216

  More actions

  January 14, 2003 at 2:21 pm

  #446331

  Thanks David, worked a charm!!