Adding a SQLCMD Variable to your project in Visual Studio
Published 22 August 2019
You can add SQLCMD variables to your SQL Change Automation project within the SQLCMD Variables tab of project properties.
The value you provide in the Default column will be stored in the project file (and therefore shared with other team members) however the Local value is specific to your machine (stored in the non-version controlled .user file). If you leave the Local column blank, the Default will be used when deploying inside Visual Studio.
Using the $(VariableName) notation, reference the variable in a new migration script.
When using variables in your scripts, qualifiers must be used in order for your scripts to be successfully parsed and validated by the build engine.
- When using a variable within an object or database name, the variable must be enclosed by brackets, for example,
SELECT * FROM [$(MyObjectNameVariable)]
- When using a variable within a string, the variable must be enclosed by quotation marks, either with the '' single-quote or "" double-quote characters, for example,
- When using a variable that contains a numeric value, the variable must be treated like a string (as above) and cast as a numeric data type before it can be used. For example,
DECLARE @myNumber int = CAST('$(MyNumberVariable)' AS int)
When you build the project, the variable will be substituted with the Default value (or Local value, if it was provided).