Database Projects – Working with SQLCMD variables

When I started to explore and use Database Projects, I ran into a specific situation quite fast where I was required to use SQLCMD variables. In this blog post, I will describe what they are, how you can use SQLCMD variables in Database Projects and where this might become very useful for you.

Quick Overview

Use Case Description

What are SQLCMD variables?

Practical Example

Use Case Description

A couple of years ago, I was working on a project where we were rewriting an existing ELT solution. In general, we were first getting the data to a landing zone and afterwards we were using Stored Procedures to push the data from the landing zone to the data warehouse which were located on the same machine.

All our business logic was written in Stored Procedures. In the first steps, we were transforming our data and making sure to get it into the right format and finally we were pushing it to the specific database tables.

Example:

MERGE dwh.dim.Store AS U
USING (
		SELECT	  s.shopnr AS StoreNumber 
				, s.shopname AS StoreName
				, s.location AS StoreLocation
				, CASE	WHEN DATEDIFF(DAY, -365, s.opening_date) > 365 THEN 'Y' 
						ELSE 'N' 
				  END AS  LikeForLike  
		FROM landing.shop AS s
) AS I (StoreNumber, StoreName, StoreLocation, LikeForLike)
ON (U.StoreNumber = I.StoreNumber)
WHEN NOT MATCHED BY TARGET THEN
	INSERT(StoreNumber, StoreName, StoreLocation, LikeForLike)
	VALUES(StoreNumber, StoreName, StoreLocation, LikeForLike)
WHEN MATCHED THEN
UPDATE
	SET   U.StoreName = I.StoreName
		, U.StoreLocation = I.StoreLocation
		, U.LikeForLike = I.LikeForLike
;	

As you can see in the code example above, we were using hardcoded database names in our scripts. At a previous employer, they always required us to have the environment name included in the database name, for example, dwh_development, dwh_acceptance, dwh_production, dwh…

Since we want to be able to deploy our Database Project to different environments, we need to find a way to specify the database name dynamically.

What are SQLCMD variables?

SQLCMD variables allow you to dynamically determine certain configuration values during the Build process of a Database Project. In the case of my specific use case, I would be able to decide on my target database name during the Build and deployment process.

You can find more information about SQLCMD variables via the following link: Database Project Settings – SQL Server Data Tools (SSDT) | Microsoft Learn

Practical Example

As shown in the example code, we are referencing a hard-coded database name, which is not preferable, to solve this, the first step we will take is opening our existing Database Project.

To continue, I navigate to my DWH_Landing Project where the hard-coded database name exists, right-click on the Database Project and choose Properties… By doing this following window will appear in Visual Studio:

We now look for SQLCMD variables, select this option, and we are going to define a SQLCMD variable. To get this done, use the following syntax:

$(VARIABLENAME)

For this example, I will create a SQLCMD variable for my data warehouse database name, in the visual below you can see what I have done.

As you can see, I have created a SQLCMD variable called DWH_DB. As you can see I have used a $-sign and brackets and set my variable name in between. By doing this you will be able to set the variable value during the build and deployment process.

To finalize this step we save our changes and close the properties window.

Now you can choose how to proceed, in my opinion, there are 2 different possibilities:

1. Use the SQLCMD Variable directly in your scripts, be aware, if you are referring to your data warehouse dimension table in multiple scripts you will need to use the SQLCMD variable in all different scripts
2. Create a synonym where you define the full path to the data warehouse dimension table including the SQLCMD variable

To be fair, I always go for option 2. In the following section, I will show you how to implement option 2 successfully. The reason why I go for option 2 is just that I only need to define the SQLCMD variable for every remote object once.

So as a first step, I will be creating a synonym in my DWH_Landing Database Project which will be looking like this:

As you will see, our newly created synonym with the SQLCMD variable will be underlined in blue which means Visual Studio cannot find the referred object. Don’t worry, deployment will go fine in the end.

As a second step, we navigate to our existing Stored Procedure which is referring to the hardcoded database name and we replace that reference with the newly created synonym.

Now it is time to get this change deployed. So we right-click on our DWH_Landing Project and choose the Publish… option.

In the Publish Database window, we now can see the option to define the SQLCMD variable. Since I want to refer to my Demo_DWH database, I will use that as a value.

To finalize push the Publish button. To show you the end result, I have opened my local instance of SQL Server 2019 and I have scripted out my synonym.

As you can see we have determined the External Database name during the deployment process instead of the development process. This technique allows you to include environment names in your database if required.