SQLCMD variables can be a useful way of having changeable parameters for your SQL scripts, allowing you to specify the value from a command line, but also to control things you couldn’t manage through a SQL variable.
They can also be a little confusing the first time you see them.
Here’s an example::SETVAR MySQLCMDVar "Wibble" SELECT * FROM dbo.Test WHERE TextValue='$(MySQLCMDVar)';
If you just run this then you might get the error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘:’.
The important thing is to understand that when you see a colon at the beginning of a statement in your T-SQL script, then this is a SQLCMD statement, and to parse these in SSMS you need to have SQLCMD mode enabled. You can do this from the “Query” menu:
Now I execute my script again, and it runs fine – though it returns no results.
Let’s look at another quick example:
Here I’ve used the SQLCMD variable to define the name of the table in my query. This is the power of SQLCMD variables, you couldn’t do this with a normal SQL variable – the only way to do that would be to use dynamic SQL instead.
Let’s understand a little deeper what they are and how they work.
First of all, they don’t have a datatype, they are actually always text values. You can enclose them in double quotes or not –but I usually prefer to – although if you have spaces or other special characters then quotes are required.
You define them as follows:
:SETVAR SQLCMDVariableName “Whatever value you want”
And where you want to refer to them in your script you use a dollar sign and the variable name in brackets:
Rather than being a conventional form of variables, SQLCMD variables are actually tags for text replacement. It’s handy to understand this as it leads to some strange behaviours. What happens when you run a query with SQLCMD enabled, is that first of all the script is parsed and any SQLCMD statements are processed.
In the case of SQLCMD variables, first all the :SETVAR statements in the script are processed and each variable is assigned the correct value. Then all the references to each variable in the script are replaced with the literal value, it is then this modified version of your script (which you never get to see) which gets executed.
That’s why something like this doesn’t work::SETVAR TextVal "Hello There" DECLARE @TextVal varchar(30); SET @TextVal = $(TextVal); SELECT @TextVal;
When I run this I get an error:
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ‘There’.
What’s going on? Both my SQL and SQLCMD variables are text aren’t they? Why doesn’t that work?
The answer lies in what I said before, the reference to a SQLCMD variable is just a tag to be replaced with the value defined elsewhere in the script. So in the above example what actually gets executed is:DECLARE @TextVal varchar(30); SET @TextVal = Hello There; SELECT @TextVal;
Which isn’t valid SQL. What I should have done in my original SQL is to wrap the reference to the SQLCMD variable in single quotes::SETVAR TextVal "Hello There" DECLARE @TextVal varchar(30); SET @TextVal = '$(TextVal)'; SELECT @TextVal;
Now it works:
I mentioned you could pass SQLCMD variables from the command line – this can be handy if you’re executing scripts and you want to (for instance) specify the database name from outside. Watch out though, if you also assign a value in your script then it is the last value assigned that gets used.
I had a developer come to me complaining that SQL wasn’t picking up the SQLCMD variable he was passing through the command line, the answer was that he had another value assigned in the script. He thought that was dumb, so I asked the question “What would you expect to happen if you were writing C# code and assigned a value to a variable, and then assigned a new one to it – which would you expect it to hold – the first or the second?”
That doesn’t mean however that assignment of values to SQLCMD variables doesn’t display some counterintuitive behaviour. Look at the following query::SETVAR TextVal "Hello There" SELECT '$(TextVal)'; :SETVAR TextVal "GoodBye" SELECT '$(TextVal)';
So I set a value in my SQLCMD variable, output it with a select statement, then I change the value and output it again. Let’s look at the output:
What the…?! I’ve encountered issues before where I’ve tried to change the value of a variable and – having done something wrong – the value hasn’t updated. But here it looks like the first query is looking into the future!
This goes back to what I said earlier, first the :SETVAR statements are processed and the variable evaluated, only then are the references replaced in the script. This means you can’t have changing values for your SQLCMD variable throughout the execution of your script.
You can even see the same behaviour if you do this::SETVAR TextVal "Hello There" SELECT '$(TextVal)'; :SETVAR TextVal "GoodBye" SELECT '$(TextVal)'; :SETVAR TextVal "See you Later!"
I’ve said you can’t change the value of your SQLCMD variable through your script, technically it’s more accurate to say you can’t have different values within the same batch. So if you separate your script into separate batches using the GO statement, then you get a different result:
You might therefore think that the SQLCMD variable is only valid in the context of the batch in which is defined. So if I remove the :SETVAR in the second batch my script will fail:
We see from this that a SQLCMD variable is not limited to the scope of a single batch – even though it gets re-evaluated on a batch by batch basis.
I’ll finish with something you might have attempted to do at some point. How about if I conditionally try to change a SQLCMD variable::SETVAR TextVal "Hello There" IF 1=0 BEGIN PRINT 'Whoah!' :SETVAR TextVal "Maths is Broken" END; SELECT '$(TextVal)';
If I’ve not confused you too much with the above examples you can probably figure out what the output will be. That’s right:
This has just reminded me of a quote from “The Hitchhikers Guide to the Galaxy“ about the babel fish, particularly the last line:
“Now it is such a bizarrely improbable coincidence that anything so mind-bogglingly useful could have evolved purely by chance that some thinkers have chosen to see it as the final and clinching proof of the non-existence of God.
The argument goes something like this: “I refuse to prove that I exist,'” says God, “for proof denies faith, and without faith I am nothing.”
“But,” says Man, “The Babel fish is a dead giveaway, isn’t it? It could not have evolved by chance. It proves you exist, and so therefore, by your own arguments, you don’t. QED.”
“Oh dear,” says God, “I hadn’t thought of that,” and promptly vanishes in a puff of logic.
“Oh, that was easy,” says Man, and for an encore goes on to prove that black is white and gets himself killed on the next zebra crossing.”
The main take home from all this should be to avoid trying to use a SQLCMD variable like a normal one. Assign it once, at the top of your script or in a command line – then leave it alone!
My contribution this week is about overcoming the variable limitations of SQLCmd mode. Most people know what SQLCmd is, the command line SQL client utility for running T-SQL, or perhaps if you are a PowerShell enthusiast you may be more familiar with its little brother invoke-sqlcmd. But perhaps you are not aware that you can take advantage of SQLCmd functionality in scripts you run directly in SQL Server Management Studio (SSMS). Unfortunately, along with the benefits of using SQLCmd mode, you also get the limitations. The limitation I’m going to talk about here is one that I had to figure out how to overcome recently, and I’m going to share what I learned with you today.
SQLCmd Mode and Variable LimitationsIf you’re not familiar with SQLCmd or SQLCmd mode in SSMS, take a look at the different options you get with SQLCmd in Books Online. To enable SQLCmd mode in SSMS, put the focus on the query window, click Query in the menu, and click SQLCMD Mode. SQLCmd commands will be automatically highlighted for you if you already have some in the script.
One of the commands that makes SQLCmd mode really appealing is the Connect command. This command allows you to connect to a specific server inline in the script which is great for those times when you need to do something that requires jumping back and forth between multiple servers. I was recently tasked with writing a script in T-SQL (please don’t ask why I didn’t use PowerShell, the project requirement was T-SQL) to automate setting up and taking down Peer-to-Peer replication which has this need.
The downside of the Connect command is that the command is processed immediately when the script batch runs and not where it occurs inline. So if you have multiple Connect commands in the script batch, they will all be processed immediately, and then it will process the remainder of the commands in the batch. Did you notice that I keep saying “batch”?
The key to using multiple Connect commands is that you have to use them in different batches separated by the GO batch separator. The batch separator is actually configurable, but the default is GO and changing it is a topic for another day. This raises another limitation though. I needed to assign values to variables to use throughout the script.
T-SQL variables are batch scoped, and I cannot use them across batches. So if I want to be able to assign some constant variables (such as database name) to use throughout the script, I would have to re-declare them in each batch. What a pain and poor user experience that would be. This was the limitation I needed to overcome. I needed to be able to declare variables once and use them across batches without re-declaring them.
The solution is that I learned that SQLCmd variables are scoped to the the entire connection and persist across batches. YAY!
Using SQLCmd variables is a little different than using T-SQL variables. You reference SQLCmd variables by enclosing them in parenthesis preceded by a dollar sign ($). So instead of a variable like @DBName, I would initialize the variable as DBName and reference it in the script as $(DBName).
In T-SQL, this would look like:Declare @DBName sysname = N'MyDB';
In SQLCmd mode, like this::SETVAR DBName "MyDB"
SQLCmd variables are processed immediately at run-time before the T-SQL is compiled. As a result, I can use a SQLCmd variable just like I could the value itself. The really cool thing about this is that I can use the DBName SQLCmd variable as the database name inline as if it was the name itself.
This is not a valid T-SQL query:Select * From @DBName.sys.data_files;
This is a valid T-SQL Query when executing it in SQLCmd mode:Select * From $(DBName).sys.data_files;
Likewise, I could use it in a use command:USE $(DBNAME);
Or inline in a string without have to concatenate the variable to the string:RaisError('Error in database $(DBName).', 15, 1);
But by far the coolest aspect of the SQLCmd variables is that they are connection scoped and persist across batches. I can assign the value once and reuse it across multiple batches without having to re-declare them.
To demonstrate what I mean, the following T-SQL script would fail to compile due to the variable not being declared in subsequent batches:Declare @DBName sysname = N'MyDB'; Select * From sys.databases Where name = @DBName; GO Select * From sys.databases Where name = @DBName;
In order for this to work, I would have to declare the variable and assign it a value in every batch. Like this:Declare @DBName sysname = N'MyDB'; Select * From sys.databases Where name = @DBName; GO Declare @DBName sysname = N'MyDB'; Select * From sys.databases Where name = @DBName;
If running in SQLCmd mode, the following T-SQL script would work perfectly::SETVAR DBName "MyDB" :SETVAR Server1 "MyServer1" :SETVAR Server2 "MyServer2" :Connect $(Server1) Select * From sys.databases Where name = N'$(DBName)'; GO :Connect $(Server2) If Exists (Select 1 From sys.databases Where name = N'$(DBName)') Begin Select * From $(DBName).sys.database_files; End
Yes, there are some limitations with using SQLCmd mode, but it also enables us to do some pretty cool things that we just can’t do with straight T-SQL. Figuring out how to work within these limitations and to take full advantage of the benefits, we can do a lot of things we thought we couldn’t.
I hope you learned something just like I did. And I hope you feel compelled to use SQLCmd mode when it is appropriate in some of your scripts.