Using parameters in a DECLARE CURSOR statement

• David D-215270

  Grasshopper

  Points: 17

  More actions

  April 1, 2005 at 3:13 am

  #66083

  Hi, I want to use parameters in a DECLARE CURSOR statement.

  The following code appears to work right, except for the ELSE-part:

  ---

  DECLARE @vanbignum varchar(15)

  DECLARE @totbignum varchar(15)

  --SET @vanbignum = '10094'

  --SET @totbignum = '10094'

  SET @vanbignum = 'tabel'

  SET @totbignum = 'conv_sel_bignrs'

  IF @vanbignum <> 'tabel' 

     DECLARE mycursor CURSOR FOR

     SELECT  bignr, teller, zin, sleutel, coderij, dampen1, dampen2, dampen3, dampen4, dampen5, [datum reactie]

     FROM    ODB_Reactie_conv

     WHERE  bignr >= @vanbignum AND bignr <= @totbignum

     ORDER BY bignr, cast(teller as int)

  ELSE

     DECLARE mycursor CURSOR FOR

     SELECT  bignr, teller, zin, sleutel, coderij, dampen1, dampen2, dampen3, dampen4, dampen5, [datum reactie]

     FROM    ODB_Reactie_conv

     INNER JOIN @totbignum on ODB_Reactie_conv.[bignr] = @totbignum.[big nr]

     ORDER BY bignr, cast(teller as int)

  OPEN mycursor

  CLOSE mycursor

  DEALLOCATE mycursor

  ---

  I get the following error: "Must declare the variable @totbignum." So I want here to make use of dynamic sql in a DECLARE CURSOR statement. The use of a tablename as a parameter causes some errors!

  Could anyone help me with this problem? Thanx in advance!

• wz700

  SSCertifiable

  Points: 7133

  More actions

  April 1, 2005 at 5:38 am

  #550050

  use dynamic sql to do this in global cursor (default).

  part of code would look like:

  declare @cursorsql varchar(4000)

  select @cursorsql =

  'DECLARE mycursor CURSOR FOR '+

  'SELECT  bignr, teller, zin, sleutel, coderij, dampen1, dampen2, dampen3, dampen4, dampen5, [datum reactie]' +

  '   FROM    ODB_Reactie_conv '+

  '   INNER JOIN ' + @totbignum + 'on ODB_Reactie_conv.[bignr] = @totbignum.[big nr]' +

  '   ORDER BY bignr, cast(teller as int)'