Sql Server: How to use variables in SQL Script in SSMS ( with SQLCMD mode)

Quick one today:

Frequently, we run into situations, where we need to put place holders, in a SQL script, that take different values based on some conditions or  settings. Essentially having variables about the script itself (and not the code)

We are taking about variables at the meta-level (script file level, and not the variables in the code itself)

SQLCMD mode within SSMS allows us to set placeholders / variables that will take new values (as you provide) right before executing the script.

First enable SQLCMD mode in SSMS:

In the query window, write some code as below:

--
--  Set variables in a script
--
:SETVAR DatabaseName "master"
:SETVAR TableName "MSreplication_options"

USE $(DatabaseName)
SELECT TOP 10 *
FROM syscolumns

USE $(DatabaseName)
SELECT *
FROM sys.objects
WHERE name LIKE '$(TableName)'
GO

Observations:

Variable replacement occurs both in

USE $(DatabaseName)

and

WHERE name LIKE ‘$(TableName)‘

• Having single quotes does not affect variable replacement, as this replacement occurs at the script file level (and node code level — if that makes sense).
• Since this is a universal replacement capability, it allows you to use variable replacement anywhere in the code without need for special escape characters.

Hope this helps,
_Sqltimes

Advertisements