title: Declare Scalar variable
body: <p>I am writing a dynamic SQL statement that is failing with the error </p>
  
  <blockquote>
    <p>Must declare the scalar variable "@@config"</p>
  </blockquote>
  
  <p>I have tried to break the code down and can confirm that the following code does return results: </p>
  
  <pre><code>Declare @@sql as nvarchar(max);
  Declare @SourceDatabase as nvarchar(10);
  Declare @DestDatabase as nvarchar(10);
  Declare @@Harray as nvarchar(max) ;
  
  Set @SourceDatabase = 'D_EXP_CPM';
  Set @DestDatabase = 'T_EXP_CPM';
  
  set @@sql = 'SELECT B.Hierarchy_SKey AS ''NEW'', A.Hierarchy_SKey AS ''OLD''
               INTO ##TEMPHCONVERT
               FROM ' + @SourceDatabase + '.[dbo].[DIM_HIERARCHY] A 
               INNER JOIN ' + @DestDatabase + '.[dbo].[DIM_HIERARCHY] B 
                     ON A.Hierarchy_Desc = B.Hierarchy_Desc
               WHERE A.Hierarchy_SKey in (' + @@Harray + ');'
  
  exec (@@sql);
  
  Declare @@config as NVARCHAR(MAX);
  
  SELECT @@config = COALESCE(@@config + ', H' + cast(A.[NEW] as nvarchar), 'H' + cast(A.[NEW] as nvarchar)) 
  FROM ##TEMPHCONVERT A;
  
  
  Print @@config
  </code></pre>
  
  <p>Which returns the value: "H15, H16, H17, H18"</p>
  
  <p>When this is plugged into the dynamic SQL statement though I get the error...</p>
  
  <pre><code>Declare @SourceDatabase as nvarchar(10)
  Declare @DestDatabase as nvarchar(10)
  Declare @@sql as nvarchar(max) --variable to hold sql statements for this stored proc
  Declare @@Harray as nvarchar(max) -- variable to hold hierarchy array elements from source
  
  Set @SourceDatabase = 'D_EXP_CPM';
  Set @DestDatabase = 'T_EXP_CPM';
  
  Declare @@config as NVARCHAR(MAX);
  
  set @@sql = 
  'SELECT 
  B.Hierarchy_SKey AS ''NEW'', A.Hierarchy_SKey AS ''OLD''
  INTO ##TEMPHCONVERT
  FROM ' + @SourceDatabase + '.[dbo].[DIM_HIERARCHY] A 
  INNER JOIN ' + @DestDatabase + '.[dbo].[DIM_HIERARCHY] B 
  ON A.Hierarchy_Desc = B.Hierarchy_Desc
  
  WHERE A.Hierarchy_SKey in (' + @@Harray + ');'
  
  exec (@@sql);
  
  set @@sql = '
  UPDATE A SET A.[Value_Text] = (SELECT @@config = COALESCE(@@config + '', H'' + cast(A.[NEW] as nvarchar), ''H'' + cast(A.[NEW] as nvarchar)) 
  FROM ##TEMPHCONVERT A)
  FROM ' + @DestDatabase + '.[dbo].[DIM_CONFIG] A ON A.[Config_Desc] = ''JBCODE FTE Map Hierarchy  List'' ;' 
  
  exec (@@sql);
  </code></pre>
  
  <p>Note I've cut quite a bit of code out of the middle here to make the post as short as possible. Can anybody explain why I'm getting the error? </p>
  
  <p>I've tried declaring the variable inside the dynamic SQL statement in two places but neither allowed the code to run. I'm thinking maybe the variable needs passing somehow but I'm not sure how to do that through to the sub query. </p>