By: Daniel Farina | Comments (4) | Related: 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.
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.
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.
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.
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.
Next Steps
- Feel free to ask any questions you have on this tip in the Q&A section below.
- Read my previous tip for an Introduction to sqlcmd Utility: Introduction to SQL Server's sqlcmd utility.
- Also you can read this other tip I wrote about executing script files with sqlcmd: Execute SQL Server Script Files with the sqlcmd Utility.
- If you need to execute multiple files with sqlcmd, you can take a look at Armando’s tip: Using SQLCMD to Execute Multiple SQL Server Scripts.
About the author
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
