How to set SQLCMD variables dynamically in SQL Server Database projects?

Question

I'm trying to set SQLCMD variables inside an script file (rather than the project properties). Wondering if it's possible for both system-defined and custom variables. I most like to change $(DatabaseName) which is system-defined.

For this scenario, I've created a JSON file which contains the key/values for variables I want to set (Key DatabaseName).

{
  "EnvironmentDetails": {
    "ProjectSettings": {
      "DatabaseName":  "TicketingDB"
    },
    "AppSettings": {
      "Name": "AppName"
    }
}

Then, in a script file, I read the JSON content and try to change the variable using :setvar

DECLARE @SQL NVARCHAR(MAX);
DECLARE @Environment NVARCHAR(MAX);
DECLARE @FilePath NVARCHAR(MAX) = '$(ProjectPath)' + '\Environment.json';
DECLARE @CustomDatabase NVARCHAR(MAX);
SET @SQL = N'
SELECT @Result = BulkColumn FROM OPENROWSET(BULK ''' + @FilePath + ''', SINGLE_NCLOB) JSON;
';
EXEC sp_executesql @SQL, N'@Result NVARCHAR(MAX) OUT', @Environment OUT;

SELECT @CustomDatabase = [value] FROM OPENJSON(@Environment, '$.EnvironmentDetails.ProjectSettings') WHERE [key]='Name'
 :SETVAR DatabaseName @CustomDatabase 

But when I check the build script, there is no change in the project.

My Goal is to provide an Environment file (similar to .env files in web projects) and set project properties through a JSON data file and a script file (rather than setting project properties for each clone of my project)

I'm new to database projects, will appreciate any guides about my misconceptions and mistakes

Answer 1

SQLCMD variable values are set by the tool (i.e. SQLCMD or SqlPackage) before the script executes so you can't change the value dynamically via T-SQL. Instead, you need to pass the variable values to the tool, such as via the v command-line argument.

Answer 2

But when I check the build script, there is no change in the project.

That's a pre-deployment script? That script isn't evaluated/executed during build, but on deployment. Have you tested the deployment to see if your script works as expected?

Answer 3

Your sample JSON missed a closing curly bracket:

{
  "EnvironmentDetails": {
    "ProjectSettings": {
      "DatabaseName":  "TicketingDB"
    },
    "AppSettings": {
      "Name": "AppName"
    }
  }
}

The issue is in the SELECT statement to open the json. Try this:

SELECT @CustomDatabase = [value] FROM OPENJSON(@Environment, '$.EnvironmentDetails."ProjectSettings"')