How to use a Database Variable Name in dynamic SQL with visual studio for a SQL Server Project?

Question

Is there a way in Visual Studio to use a Database Variable Name in dynamic SQL for a SQL Server project? Or, is there a alternative solution?

I have a project for a SQL Server database with references to other database projects, each with Database Variable Name set. For example, the Database Variable Name would be $(PROD) and I have used this Database Variable Name to publish the project using a TEST database.

My current project is using dynamic sql, and I would like to be able to publish this project using either a PROD or TEST database. I would prefer not to use a @database variable, and simply using SELECT @database = $(PROD) does not work.

For example, below I have dynamic SQL in a stored procedure with QUOTENAME(@database), and I would like to be able to replace the @database variable and use $(PROD) or some way to set the database name when I publish the project.

DECLARE 
  @sql NVARCHAR(MAX),
  @database SYSNAME,
  @schema SYSNAME,
  @table SYSNAME;
CREATE TABLE #src_pk(SrcColumn VARCHAR(100));

SELECT @sql = N'SELECT QUOTENAME(b.[COLUMN_NAME]) AS [Column] 
                  FROM ' + QUOTENAME(@database) + N'.[INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] a 
                  JOIN ' + QUOTENAME(@database) + N'.[INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE] b 
                    ON a.[CONSTRAINT_NAME] = b.[CONSTRAINT_NAME] 
                 WHERE a.[CONSTRAINT_SCHEMA] = ' + QUOTENAME(@schema, '''') + N'
                   AND a.[TABLE_NAME] = ' + QUOTENAME(@table, '''') + N'
                   AND a.[CONSTRAINT_TYPE] = ''PRIMARY KEY'' '
INSERT INTO #src_pk EXEC sys.sp_executesql @sql;

I am using Visual Studio 2019; the project language is in SQL; and, my project platform is SQL Server 2017.

Answer 1

You can use the project deployment variable directly in the dynamic SQL, just be sure to correctly quote it:

SELECT @sql = N'SELECT QUOTENAME(b.[COLUMN_NAME]) AS [Column] 
                  FROM ' + QUOTENAME('$(DatabaseName)') + N'.[INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] a 
                  JOIN ' + QUOTENAME('$(DatabaseName)') + N'.[INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE] b 
                    ON a.[CONSTRAINT_NAME] = b.[CONSTRAINT_NAME] 
                 WHERE a.[CONSTRAINT_SCHEMA] = ' + QUOTENAME(@schema, '''') + N'
                   AND a.[TABLE_NAME] = ' + QUOTENAME(@table, '''') + N'
                   AND a.[CONSTRAINT_TYPE] = ''PRIMARY KEY'' '