Variable inside another variable in SQLCMD mode

Question

Trying to tidy up my SQL script with SQLCMD mode and I ran into an issue:

:setvar db_suffix "some_suffix"
:setvar some_db "some_db_$(db_suffix)"
print 'some_db: $(some_db)'

The output of that is:

some_db: some_db_$(db_suffix)

However, what I expected was:

some_db: some_db_some_suffix

Is there a way to have variable interpolation like this?

(Note that T-SQL functions such as CONCAT() won't work since I'll be using the variable as a database name).

Answer 1

Is there a way to have variable interpolation like this?

Kinda sorta, in a way. Just not directly.

You need to keep in mind a few things:

1. The value that you set a variable to via :setvar is a simple, literal value. You can see this by running just the following two lines:

   :setvar var1 $(var2)
   PRINT '$(var1)';
   

   which returns:

   $(var2)
   

   If SQLCMD / SQLCMD-mode attempted to parse the value portion of :setvar in any way, then it would error on $(var2) not being defined.

2. Variable substitution is allowed in some other SQLCMD commands.

   This means that you can, for example, execute a shell command while passing a SQLCMD variable to that command line:

   :setvar db_suffix some_suffix
   !! echo :setvar other_db [MyDB_$(db_suffix)] > c:\TEMP\setvar.txt
   

   The two lines above will create / overwrite a file, C:\TEMP\setvar.txt, with the text of ":setvar other_db [MyDB_$(db_suffix)]" where $(db_suffix) is replaced with its value of "some_suffix".

3. SQLCMD commands in a file / script that is imported via the :r command are processed in a second-pass, allowing for variables set in those external files to be reflected in the main script:

   :setvar db_suffix some_suffix
   !! echo :setvar other_db [MyDB_$(db_suffix)] > c:\TEMP\setvar.txt
   :r C:\TEMP\setvar.txt
   PRINT 'somethin_somethin: $(other_db)';
   

   Returns:

   somethin_somethin: [MyDB_some_suffix]
   

4. SQLCMD commands are interpreted per-each batch !! So if you want to change the concatenated value, then you need to separate each creating and reading of the temp file, else the value you get will be the last one to be set since all of the !! and :r commands will be processed before the variable values are substituted in, and then the batch can be submitted to SQL Server to process the T-SQL. For example:

   :setvar db_suffix some_suffixes
   !! echo :setvar other_db [MyDB_$(db_suffix)] > c:\TEMP\setvar.txt
   :r C:\TEMP\setvar.txt
   PRINT 'somethin_somethin: $(other_db)';
   --GO
   !! echo :setvar other_db [NotMyDB_$(db_suffix)22] > c:\TEMP\setvar.txt
   :r C:\TEMP\setvar.txt
   PRINT 'somethin_somethin2: $(other_db)'
   

   Will return:

   somethin_somethin: [NotMyDB_some_suffixes22]
   somethin_somethin2: [NotMyDB_some_suffixes22]
   

   BUT, uncomment the --GO and you will get the following:

   somethin_somethin: [MyDB_some_suffixes]
   somethin_somethin2: [NotMyDB_some_suffixes22]
   

Answer 2

Actually this should be just a comment to Solomon Rutzky ́s answer. But Im not allowed to do that. Thank you - works perfect.
But when using :on error exit the script fails, if the target var is not defined before. I think this is in the 1st run - before the :r setvar.txt was done.
So I added a :set MyVar nil in front and everything was fine.

:setvar MyVar nil
!! echo :setvar MyVar from_$(OtherVar)_ > setvar.txt
:r setvar.txt
print 'MyVar=[$(MyVar)]'

Answer 3

a bit tricky

:setvar var2 'aaa'
:setvar var1 "'$var2'"
PRINT '$var2 =' + $(var2);
PRINT '$var1 =' + $(var1);
PRINT '$var1 =' + REPlACE( $(var1) , '$var2' , $(var2) );

OUTPUT

$var2 =aaa
$var1 =$var2
$var1 =aaa

• Colin 't Hart: It's not clear how this answers the question. Please edit so that it produces the output the asker is expecting.
• SpaceGhost440: It did but it wasn't quite clear. I rewrote his example. He's using his own pseudo token, then replacing it with the actual value. :SETVAR var1 "I LOVE LAMP" :SETVAR var2 "I LOVE WHAT?: $var1" PRINT '$(var2)' PRINT REPLACE( '$(var2)', '$var1', '$(var1)')