title: Using Variable with Dynamic SQl
body: <p>Hi I am using Dynamic Query in My store Procedure Using following syntax</p>
  
  <pre><code>`   
  
      set @query =N'Select  ComponentName, '+ @cols +' From (
      Select Sum('+ @Sum_cols +') As Comp_stock,Com.ComponentName ,BB.BB_Name  
      from Z_DM_DR_CM_STOCK COM
      Inner Join BLOOD_BANK_MASTER BB on COM.BB_srno =BB.BB_SRNO
      where Com.Trans_date ='''+ Convert(Varchar(10), @Trans_Date,126) +'''
      AND   (  @BG  = ''''OR  COM.BGName =''' + @BG +'''
  
      )Group by Com.ComponentName,BB.BB_Name  ' +
      ')As sourcetable 
      PIVOT
      (
      SUM([Comp_stock]) FOR [BB_Name] IN ('+ @cols +')
      ) As PIVOTTABLE order by ComponentName'
  
    execute(@query)`
  </code></pre>
  
  <p>BUt its giving error </p>
  
  <p>Must declare the scalar variable "@BG".</p>
  
  <p>and @BG is Input Parameter </p>
  
  <p>Can any one help?</p>