Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
The Trade-off Between SQL Server Security and Performance - Free Webinar
 

sqlcmd User Variables and Variable Precedence Examples


By:   |   Last Updated: 2017-08-24   |   Comments   |   Related Tips: More > Database Administration

Problem

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.

Solution

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)

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
     ,LastName
FROM $(UserTable)
GO

:setvar UserTable AdventureWorks2012.Sales.vIndividualCustomer

SELECT TOP 5 FirstName
     ,LastName
FROM $(UserTable)
GO

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)'
GO
:setvar MyVar “this is “”my”” variable”
SELECT '$(MyVar)'
GO

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


Last Updated: 2017-08-24


next webcast button


next tip button



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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools