[SOLVED] VS2017 DBPROJ SQLCMD Variable Test for Literal Value

VS2017 DBPROJ SQLCMD Variable Test for Literal Value

I want to conditionally run a post-deploy script. My script (currently) looks like this:

declare @tier nvarchar(100) = $(tier)
if( @tier = 'TEST' )
    begin
    :r .\ConfigSeedData.TEST.sql
    end

I have defined the variable in the project file, as detailed in Using variable in sql postdeployment build script?. The variable is (currently) defined as TEST (no quotes, but I've tried it with quotes, too).

However, no matter how I try to twist my syntax, I keep getting this error: .Net SqlClient Data Provider: Msg 207, Level 16, State 1, Line 875 Invalid column name 'TEST'.

How do I check a sqlcmd variable for a literal value, not a column?

I have tried adding the square brackets around the $(), tried accessing the value directly (without assigning to nvarchar).

Thanks!!

Solution

So it turns out, you need to put single quotes around the variable use. To make the above work, the code would be:

if( '$(tier)' = 'TEST' )

So even though it looks like you're using the literal "$(tier)", it's actually being pulled out of the quotes, interpreted, and put back in.