Transact SQL – (Global) script variables


When running a SQL script using the command sqlcmd tool if you switch context to another database, by using the “use database” command, then any variables defined before this are lost. A similar thing happens if you use the “go” command to group, or batch, commands in the script.

Solution 1:

Create a temporary table, e.g

CREATE TABLE #GlobalVariables (variableOne varchar(30), variableTwo int)
INSERT INTO #GlobalVariables (variableOne, variableTwo) VALUES ('ABCdef', 222222)

Solution 2:

Use script variables

:setvar variableOne "ABCdef"
:setvar variableTwo 222222

To access these in your SQL code you would do the following:

UPDATE #GlobalVariables SET variableOne = '$(variableOne)', variableTwo = $(variableTwo)