Dynamic SQL within Cursor

• bshaw

  Right there with Babe

  Points: 750

  More actions

  September 10, 2003 at 11:17 am

  #82847

  Looking for help with Dynamic SQL. Within a SP, I create a temp table. Then create a cursor for the purpose of updating a field in the temp table.

  I know my syntax is not correct. This is where I need help. "Must declare cursor @BR" is the error message returned.

  Thanks for your help.

  Here is an example.

  --Temp Table

  Create Table #tmpAverages

  (

  Branch varchar(2),

  RDpart varchar(15),

  PD varchar(2),

  Qty int

  )

  --Cursor

  DECLARE @SQLString nVarchar(3000)

  DECLARE @BR varchar(2)

  DECLARE BR_Cursor CURSOR FOR

  Select Distinct BranchCode from tblAveragesDtl

  WHERE BranchCode IS NOT NULL AND LEN(RTRIM(BranchCode)) <> 0

  OPEN BR_Cursor

  FETCH NEXT FROM BR_Cursor

  INTO @BR

  WHILE @@FETCH_STATUS =0

  BEGIN

  Set @SQLString = 'Branch = ' + '''' + @BR + ''''

  Execute sp_executesql @SQLString

  Set @SQLString = NULL

  FETCH NEXT FROM BR_Cursor

  INTO @BR

  END

  deallocate BR_Cursor

• winash

  SSChampion

  Points: 11446

  More actions

  September 10, 2003 at 1:18 pm

  #473525

  Hi,

  Are you trying to print out the various BranchCodes within the cursor??

  you might try replacing the sp_executesql with the following :

  Set @SQLString = 'Branch = ' + '''' + @BR + ''''

  Print @SQLString

  Set @SQLString = NULL

  sp_executesql does not identify the statement as a T-SQL statement and hence the error....you could alternatively try :

  SET @SQLString = 'PRINT '+'''Branch = '+@BR+''''

  Execute sp_executesql @SQLString

  HTH

• crosspatch

  Hall of Fame

  Points: 3168

  More actions

  September 17, 2003 at 8:59 am

  #474660

  Looking at your example I can't see where you are populating your temporary table.

  More importantly I'm wondering whether you really need to use a cursor to do what you want (cursors are generally bad news in terms of performance). Perhaps there is a Select statement you can use to select the data you want into the table such as

  SELECT DISTINCT 'Branch ' + BranchCode

  FROM tblAveragesDtl

  WHERE BranchCode IS NOT NULL AND LEN(RTRIM(BranchCode)) <> 0

  (I'm assuming here that the 'Branch ' + BranchCode is the data you want to generate)

  I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961

  
  I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961