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

Ben 21 Reputation points
2021-02-22T14:32:13.97+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,069 questions
{count} votes

Accepted answer
  1. Dan Guzman 9,221 Reputation points
    2021-02-22T15:00:37.373+00:00

    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.

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. Olaf Helper 42,386 Reputation points
    2021-02-22T14:48:13.337+00:00

    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?


  2. Guoxiong 8,126 Reputation points
    2021-02-22T15:01:55.4+00:00

    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"')
    
    0 comments No comments