Search code examples
sql-servert-sqlsqlcmd

Conditionally set a SQLCMD variable in a scriptfile?

I need to conditionally set a SQCMD scripting variable depending on the machine name.

I have a SQLCMD command as follows

sqlcmd  -U "username" -S "ipaddr" -P "password" -i "scriptname"

In the script scriptname, the psuedocode of what I want to achieve is:

IF SERVERPROPERTY('MachineName') = "X"
  :setvar USER User1
ELSE
  :setvar USER User2

so that the USER can be used in subsequent statements as $(USER), currently in many existing lines of code

I won't list all the failed attempts, but I can't get the correct syntax of mixing SQLCMD with TSQL.

How can I achieve this?

Solution

  • SQLCMD variable values are assigned by the tool before the batch is sent to SQL Server for execution. Consequently, it is not possible to set the value, conditionally or otherwise, using T-SQL (which runs on the server).

    One workaround is to invoke SQLCMD twice, once to create the SETVAR commands using T-SQL and pipe it to a file on the client and then to execute the final script that include the file using the ':r' SQLCMD command. Example (which could be encapsulated in an OS command file):

    sqlcmd -U "username" -S "ipaddr" -P "password" -I -Q"IF SERVERPROPERTY('MachineName') = 'X' PRINT ':setvar USER User1' ELSE PRINT ':setvar USER User1';" >"C:\Scripts\setvarcommands.sql"
sqlcmd -U "username" -S "ipaddr" -P "password" -I -i "C:\Scripts\scriptname.sql"

    Contents of c:\Scripts\scriptname.sql:

    :r "C:\Scripts\setvarcommands.sql"
PRINT '$(USER)';

    There may be more elegant ways to create the setvarcommands.sql but this is the first that came to mind.

    Note that I specified the -I SQLCMD parameter so that QUOTED_IDENTIFIERS ON is used for script execution. The default is OFF for backwards compatibility and may result in issues with some DDL or DML operations. I recommend -I' always be specified.