title: How to declare variables in T-SQL scripts that are common across multiple scripts being run
body: <p>I have a set of scripts to perform bulk data imports that I am trying to include in a single 'calling' script that uses SqlCmd mode. The issue I have is that each script includes the same set of declared vars that define paths or common objects. When I run the 'calling' script I get the error that the vars have already been declared.</p>
  
  <p>If I pull the declarations out of the individual scripts, Intellisense complains of course that they are not declared. The scripts themselves need to be run in isolation from the 'calling' script also, so ideally I would need the vars also declared in the individual scripts.</p>
  
  <p>Thank You.</p>
  
  <p>Examples:
  Common Individual Script Declaration and Initial SETs</p>
  
  <pre><code>DECLARE @path varchar(256),
          @currPeriod varchar(25),
          @pastPeriod varchar(25),
          @period varchar(25),
          @fileName varchar(256),
          @sheet varchar(25),
          @sql varchar(MAX)
  
  SET     @path = 'H:\Scripts\DataImport';
  SET     @currPeriod = CONCAT(DATEPART(year,GETDATE()),'-',CONVERT(varchar(2), getdate(), 101));
  SET     @pastPeriod =  CONCAT(DATEPART(year,DateAdd(month, -1, Convert(date, GETDATE()))),'-',CONVERT(varchar(2), DateAdd(month, -1, Convert(date, GetDate())), 101));
  SET     @period = @pastPeriod;  -- Change to currPeriod or pastPeriod based on import type.
  SET     @fileName = 'ReferenceClients-' + @period + '.xlsx';
  SET     @sheet = '[Sheet1$]';
  
  SET     @sql = 'INSERT INTO #TempRefClients
                  SELECT *
                  FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',
                                  ''Excel 12.0; Database=' + @path + '\' + @period + '\' + @fileName + '; HDR=YES; IMEX=1'',
                                  ''SELECT * FROM ' + @sheet + ''')'
  </code></pre>
  
  <p>Example of Calling Script - Run using SqlCmd Mode</p>
  
  <pre><code>-- Ref Clients
  BEGIN
      PRINT ' ';
      PRINT 'Importing Ref Clients';
      :r "H:\Scripts\DataImport\CurrentMonthScripts\BulkImport-RefClients.sql"
  END;
  
  -- Top Clients
  BEGIN
      PRINT ' ';
      PRINT 'Importing Top Clients';
      :r "H:\Scripts\DataImport\CurrentMonthScripts\BulkImport-TopClients.sql"
  END;
  </code></pre>