How to declare variables in T-SQL scripts that are common across multiple scripts being run

Question

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.

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.

Thank You.

Examples: Common Individual Script Declaration and Initial SETs

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 + ''')'

Example of Calling Script - Run using SqlCmd Mode

-- 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;

Have you looked at [scripting variables](https://msdn.microsoft.com/en-us/library/ms188714.aspx)? You can define the same scripting variables in all scripts and pass their values from the commandline or using a `:Setvar` statement. That's how SSMS and SSDT scripts work. — Panagiotis Kanavos, Mar 21 '17 at 12:48
please add some sample table data and the expected result - as well in formatted text. Also show us your current query attempt — TheGameiswar, Mar 21 '17 at 13:19

Solomon Rutzky · Accepted Answer · 2017-03-22T17:29:05.577

You should be able to accomplish this by doing three things:

Do not declare the variables in the master script. Instead, create a local temporary table:

CREATE TABLE #ConfigSettings
(
  [SomePath]        NVARCHAR(500) NOT NULL,
  [CommonObjectA]   NVARCHAR(128) NOT NULL,
  [SomeMaxValue]    INT
);

INSERT INTO #ConfigSettings VALUES (N'C:\go\here\for\some\reason\', N'objectName', 55);

In each of the included scripts that also need to be run apart from the main script, declare the variables with a script ID at the end of the variable name so that they can exist on their own or along with the other script's variables:

DECLARE @SomePath_1       NVARCHAR(500) = N'default_when_run_individually',
        @CommonObjectA_1  NVARCHAR(128) = N'default_value',
        @SomeMaxValue_1   INT = default_value;

Another script would have:

DECLARE @SomePath_2       NVARCHAR(500) = N'default_when_run_individually',
        @CommonObjectA_2  NVARCHAR(128) = N'default_value',
        @SomeMaxValue_2   INT = default_value;

After declaring the variables in each script, set them based on the values in the local temporary table:
```
IF (OBJECT_ID(N'tempdb..#ConfigSettings') IS NOT NULL)
BEGIN
  SELECT @SomePath_1 = cnf.[SomePath],
         @CommonObjectA_1 = cnf.[CommonObjectA],
         @SomeMaxValue_1 = cnf.[SomeMaxValue]
  FROM   #ConfigSettings cnf
END;
```
When each script is run individually, the variables will retain their default value. When they are run within the main script, the local temp table will exist and will override those default values.

OR

Again, do not declare anything in the main script, not even a local temporary table to hold config values.

Create an include file to be read in via :r that sets all of your SQLCMD variables:

:setvar SomePath "C:\go\here\for\some\reason\"
:setvar CommonObject "objectName"
:setvar SomeMaxValue 55

At the top of each script, read in that common include file:
```
:r C:\AppConfigStuff\CommonIncludeConfigVariables.sql
```

In each of the included scripts that also need to be run apart from the main script, declare the variables with a script ID at the end of the variable name so that they can exist on their own or along with the other script's variables, and use the SQLCMD variables as the default values:

DECLARE @SomePath_1       NVARCHAR(500) = N'$(SomePath)',
        @CommonObjectA_1  NVARCHAR(128) = N'$(CommonObject)',
        @SomeMaxValue_1   INT = $(SomeMaxValue);

Another script would have:

DECLARE @SomePath_2       NVARCHAR(500) = N'$(SomePath)',
        @CommonObjectA_2  NVARCHAR(128) = N'$(CommonObject)',
        @SomeMaxValue_2   INT = $(SomeMaxValue);

In both approaches, using unique variable names in each individual script will eliminate any conflict, especially when you have other reasons for not putting "GO" statements between the scripts.

And, in the second approach, you might could probably get away with not even needing the T-SQL variables in the first place if you replace all T-SQL variable references with the SQLCMD variable references (e.g. = $(IntVar) or = N'$(StringVar)' ).

Thanks srutzky. I went with your first option and it provided what I needed. Ultimately I have decided to migrate the scripts to SPs, but this will work in the interim. — TravisPUK, Mar 28 '17 at 06:59

How to declare variables in T-SQL scripts that are common across multiple scripts being run

1 Answers1