Syntax issue when using dynamic query

Question

Query 1:

  SET @sql2 = 'insert into TempReport   
  select ID, max(TransactionTime),0 from  ClubTransaction with (nolock)  
  where ClubcardID in (select ClubcardID from TempCC)   
  and ClubcardTransaction.OfferID  not in (119,120,121)  
  group by ClubcardID' 
  exec (@Sql2)

Query 2:

  delcare @OfferID varchar(50)
  set   OfferID='1,112,445,'  
  SET @sql2 = 'insert into TempReport   
  select ID, max(TransactionTime),0 from  ClubTransaction with (nolock)  
  where ClubcardID in (select ClubcardID from TempCC)   
  and ClubcardTransaction.OfferID not in (Select Item From dbo.fnSplit(@OfferID,'','')   
  group by ClubcardID'    
  exec (@Sql2)

Query 1 works fine. In query2 I am replacing with an variable de defined where I am passing to the function fnSplit where I split the values with comma separated. I get an error message Must declare the scalar variable "@OfferID". Please let me know where is the issue here.

Ben · Accepted Answer · 2012-05-02 12:43:59Z

2

You have to put the value from outside and replace the ' by ":

Query 2:

  declare @OfferID varchar(50)
  set   OfferID='1,112,445,'  
  SET @sql2 = 'insert into TempReport   
  select ID, max(TransactionTime),0 from  ClubTransaction with (nolock)  
  where ClubcardID in (select ClubcardID from TempCC)   
  and ClubcardTransaction.OfferID not in (Select Item From dbo.fnSplit(' + replace(convert(varchar(4000), @OfferID), '''', '''''') + ',"","")   
  group by ClubcardID'    

  exec (@Sql2)

Another solution (and a better one) is to use sp_executesql

Be aware that dynamic SQL is a way to make sql injections and you should avoid using it..

edited May 2, 2012 at 12:43

Ben

35.8k6 gold badges81 silver badges117 bronze badges

answered May 2, 2012 at 9:01

aF.

67k45 gold badges141 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Syntax issue when using dynamic query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related