Manage configurations in SQL Server Database project via SQLCMD variables

What is Database Project? 

The purpose of a Database Project is to create and manage SQL database scripts. It allows us to view quickly, design, modify, and test database objects such as tables, views, queries, and stored procedures without jumping from the Visual Studio environment to a different toolset. Effectively, a database project converts all the objects in your database into text files that can be searched, modified, and stored in source control, just like your code files.   

The main advantage of using a Database project is its design, development, and deployment productivity. It provides schema comparison functionality for deployment between different servers. It also manages the deployment of a project to multiple environments. It can easily track changes made over time with the capability of reverting to the previous version if an error has occurred. It is also an excellent place for documentation of a database which is more effective to view than in extended properties.   

Creating a New Database project 

Open Visual Studio and search for SQL Server Database Project.  

Click next and specify the name for the project. Click on the Create button.  

What is SQLCMD 

SQLCMD is a utility used for interactive execution of T-SQL statements, stored procedures, and automating T-SQL scripting tasks. It allows you to use predefined and custom-defined variables. There are various modes available to use this utility. In this article, we are using SQLCMD MODE in the Database project visual studio.   

Adding SQLCMD Variable 

To add a SQLCMD variable, right-click on the project and click Properties. In properties, navigate to the SQLCMD Variables tab and define variables here.  

Variables are defined using $(variableName) notation (refer to the screenshot below). We have defined variable name $(Env). This is the name that must be referenced in pre- or post-deployment scripts. You can add as many variables as per your requirement. 

The value in the Default column will get saved in the project file (.sqlproj), and the value in Local column will be specific to your machine and get saved in the .user file. If you have both variables filled in, then the variable Local has higher precedence over the Default for publishing. If only the Default variable is filled in, then no values will be filled in automatically until you click on the Load values button.  

Guidelines for SQLCMD variable names 

Consider the following guidelines when defining SQLCMD variable: 

• Scripting variables are not case sensitive. 
• They must not contain white space characters or quotation marks. 
• They must not have the same form as a variable expression such as $(var). 

Defining & Setting SQLCMD variables  

There are two ways to define and set variables. 

1. Using setvar command  

The following is the syntax to reference a variable in the post-deployment script.  

 :setvar TableName MyTable  

 SELECT * FROM [$(TableName)] 

2. Using -v switch 

We can define variables implicitly by using -v switch of SQLCMD. Variable names must be after–v switch, followed with an equal sign and the value:  

sqlcmd -v variable = value 

Including a file in pre- or post-deployment script 

The following is the syntax to include a file in the post-deployment script:  

:r .\myfile.sql  

Differentiation between T-SQL and SQLCMD statements 

All statements beginning with a colon in the T-SQL script are SQLCMD statements. This makes the difference between SQLCMD and T-SQL clear.  

To avoid compilation errors in the project, it is important to enable the SQLCMD mode. 

Managing Configurations at Runtime 

 SQLCMD variables can be utilized to provide a dynamic substitution for both debugging and publishing. You can use variables and values during build, pre-deployment, or post-deployment. The database objects are created using the assigned values. The following is an example of a post-deployment script that executes different scripts depending upon the value of the variable “DeployType." 

Conclusion 

Database projects, along with SQLCMD utility, make it a powerful application environment where you can develop an entire database and automate the complete development lifecycle. This article was focused on how to create SQLCMD variables and their usage.