sqlcmd User Variables and Variable Precedence Examples

By:   |   Comments (4)   |   Related: More > Database Administration


In my previous tip I showed you that sqlcmd gives us access to internal variables and commands that we can use in order to automate code execution. But it also allows us to define our own variables. In this tip I give you some examples on how to take advantage of this feature.


There is an advantage to using scripts with sqlcmd over pure T-SQL scripts, which is portability. Imagine a scenario where you have a script that needs the name of the current server or the workstation where the script is being executed. In the previous described situation, if you use a pure T-SQL script you will need to modify the references to the server and workstation for the entire script. This is not only a time consuming task, it is also prone to mistakes.

Variables on sqlcmd

Sqlcmd allows the use of variables. It gives you the possibility to define variables on your scripts, on the sqlcmd console and also using the –v switch on the command line when executing sqlcmd. Additionally, sqlcmd can use environment variables, but I will cover this topic later in the tip.

When you need to reference a variable, either a user defined variable or an internal variable, you must use the following notation where VARIABLE is the variable name:


Variable Name Restriction

There are some restrictions on the way you name a variable:

  • Variable names must not contain blank characters or quotation marks.
  • Variable names must not have the same form as a variable expression: $(variable).
  • The names are case-insensitive

Defining and Setting Values to Variables

There are two ways we can define and assign values to variables. We can define variables implicitly by using the –v switch of sqlcmd. Also we can define and assign a value to variables explicitly using the :setvar sqlcmd command.

When you define a variable implicitly you must add the variable name after the –v switch, followed with an equal sign and the desired value.

sqlcmd -v variable = value

On the other hand, if you intend to assign a value to a variable with :setvar you must not use the equal sign.

:setvar variable value

The next script shows a case example on how to set variables implicitly and explicitly:

sqlcmd -v UserTable = AdventureWorks2012.Person.Person

SELECT TOP 5 FirstName
FROM $(UserTable)

:setvar UserTable AdventureWorks2012.Sales.vIndividualCustomer

SELECT TOP 5 FirstName
FROM $(UserTable)

On the next image you will see the results of the previous script.

Variables - Description: This is how to define variables.

Be aware that in cases when you need to set a variable with a value that contains spaces you must enclose it with quotation marks. Also when the value you need to set to a variable includes quotation marks you must escape those characters. Take a look at the following examples:

:setvar MyVar “this is my variable”
SELECT '$(MyVar)'
:setvar MyVar “this is “”my”” variable”
SELECT '$(MyVar)'

The next screen capture shows the output of the previous script execution.

Quotes - Description: In some cases you must use quotes for the variable value.

Also you may ask if there is a way to remove a variable. Of course you can, you just need to set the variable to nothing.

:setvar UserTable 

On the next screen capture you can see that I set a variable named UserTable with the value “AdventureWorks2012.Sales.vIndividualCustomer" and then list the defined variables. After that I unset the variable and you may see that it doesn’t show when I recheck the defined variables.

UnsetVariables - Description: You can unset variables in sqlcmd.

Using Environment Variables Within sqlcmd

Another interesting possibility sqlcmd gives to us is the ability to use operating system environment variables, those defined both at user level and at system level. But keep in mind that you cannot modify the content of those variables in sqlcmd, they are read only. If you ask yourself why, you will find the answer when I explain variable precedence.

On the next screen capture you will see that I am using the USERDOMAIN environment variable to grant CONNECT permission to user Moxi.

Environment Variables - Description: You can take advantage of environment variables on your scripts.

Variable Precedence

Every programming or scripting language has what is called variable precedence and sqlcmd is no exception. Of course, you may be asking yourself why there is such thing named variable precedence and what does it mean; as always there is an answer for that question and we will go together finding the answer with the help of an example.

You have read on the previous headline that I used an operating system environment variable, USERDOMAIN in my script. Also you have read the variable name restrictions and there is no restriction on defining a variable named USERDOMAIN; the same name of the environment variable. Here is when variable precedence comes into the table.

Following is a list that shows the variable precedence for sqlcmd:

  • :Setvar X Y, variables defined inside the script or the scripting console.
  • sqlcmd -v X=Y, variables defined on command line invocation.
  • Command shell (SET X=Y) set at command prompt before starting sqlcmd.
  • User level environmental variables.
  • System level environmental variables.

On the following screen capture you will see that I assigned a value to a variable USERDOMAIN, which is the same name of an environment variable, and when I exit sqlcmd the USERDOMAIN environment variable was unchanged.

Variable Precedence - Description: This is an example of how variable precedence works.
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, August 27, 2021 - 10:14:48 AM - Adam Walker Back To Top (89179)
Hi . Any assistance with the following would be greatly appreciated.
I have a sqlcmd executed daily via a SQL Agent Job (in SSMS) to export the contents of a table as a pipe delimited csv to a network location. See codde below:

sqlcmd -h-1 -S pr-markt-sql-01 -d files -E -W -s "|" -k 1 -Q "set nocount on; select * ,getdate() as [Date Exported] from dbo.RIGHT" -o "\\fi.pri\dept\Slsmktg\04 Data and Insight\Data\Data Extractions and Analysis Requests\009 SQL Exports\RTBF.txt"

What I'd like to be able to do is add a variable date parameter to the name of the outputted file e.g. RTBF_yyyymmdd.text. Does anybody have any suggestions on how best to acheive this please? Thanks in advance.

Tuesday, August 17, 2021 - 11:12:02 AM - Scott Thompson Back To Top (89135)
has anybody been able to figure our how to use a ] in a sqlcmd variable

:Setvar dbname "databasename].[schemaname"

So we can use "select * from [$dbname].tablename" in the scripts.

Monday, July 13, 2020 - 1:05:19 PM - Raj Back To Top (86133)

Can you please tell me how to set values dynamically to :SETVAR 


Decalre @ScriptPath char(100);

Set @ScriptPath = 'E:\mypath\test\text.txt'

:SETVAR testPath=@ScriptVar

:OUT testPath

Monday, May 18, 2020 - 11:24:18 AM - David Sacker Back To Top (85688)

I'm coming along late on this thread, but many people have asked how to hide the "variable not defined" message for those variables you didn't (or didn't want to) set. Pretty easy by sending the StdErrorOut to the bit bucket:

SQLCMD -S YourServer -E -d YourDatabase -i YourScript 2> nul

get free sql tips
agree to terms