SQL Server sqlcmd setvar depending of selected database

I'm using sqlcmd for a moment now to update database on our project.

Before we have use script like this:

:setvar relativepath D:\Project1\Sql
:r $(relativepath)\Tables\CreateTable1.sql
:r $(relativepath)\SP\Table1.sql

This was a good way to update database during developement and publish.

But now we are using different directory depending if we are in a dev branch or a production branch (Subversion).

We wish to avoid error, in case someone comit a script to a wrong directory by setting the path depending of the selected database name. Some thing like this:

:setvar relativepath "D:\Project1\Prod\Sql"
if (DB_name() = 'Project1' OR DB_name() = 'Project1_Dev')
begin
    :setvar relativepath "D:\Project1\Dev\Sql"
end

I can't find a way to do it. Some one have an idea?

[Update] Solution : (Tanks to wBob)

I have a script named : setRelativePath.sql

:out D:\Project1\SQL\Temp\temp.sql
go

declare @bdName varchar(15) = DB_name()
--Here the print got saved to D:\Project1\SQL\Temp\temp.sql
if @bdName = 'Project1' OR @bdName = 'Project1_DEV'
    print ':setvar relativepath D:\Project1\DEV\Sql'
else if @bdName = 'Project1_PROD'
    print ':setvar relativepath D:\Project1\PROD\Sql'
else 
    raiserror( 'Unknown value (%s) for sqlcmd variable relativepath.', 16, 1, @bdName )

go
:out stdout
go

!!REM Read the file to execute the setvar statement
:r D:\Project1\SQL\Temp\temp.sql
go

--Here the print is displayed then we execute the script
print 'Relative path set to $(relativepath)'
go

then i use it in my scripts files:

:r D:\Project1\SQL\setRelativePath.sql
:r $(relativepath)\SP\SomeStoredProcedure.sql

D:\Project1\SQL\ is in the trunk .

the relativepath varriable can be used inside of SomeStoredProcedure.sql to.

This script ensure we do not aply a dev script to prod and remove a step when we merge dev to prod.

You cannot mix sqlcmd mode commands with T-SQL conditional logic. There is a way to do this, but as to whether it is advisable is another matter.

This technique involves redirecting stdout, writing out to a file and executing that file all under sqlcmd mode to get the effect you require:

:out d:\temp\temp.sql
go

declare @x varchar(5) = 'dev'

if @x = 'dev'
    print ':setvar testVar dev'
else if @x = 'prod'
    print ':setvar testVar prod'
else raiserror( 'Unknown value (%s) for sqlcmd variable testVar.', 16, 1, @x )
go
:out stdout
go

!!REM Read the file to execute the setvar statement
:r d:\temp\temp.sql
go

select '$(testVar)' env

print 'it looks like this is the $(testvar) environment after all'
go

Obviously the process running this will require access to a scratch directory and file, and there are associated permissions (and risks) with this method. The placement of GOs is important when redirecting output.

As regards usage, I would not particularly advise it. We experimented with this (hence I know this technique) but moved away from it in favour of either database projects (which handle all this for you) or re-runnable (idempotent) scripts. Rather than have a load of conditional logic in parent scripts, each object script was built to be re-runnable using 'if not exists' checks, for example:

if not exists ( the table )
  create the table

if not exists ( a column )
  add the column

Then it didn't matter if we were running in dev or prod, or how many times we ran the script. These can get a bit unwieldy after a time.

StackExchange Database Administrators Q#117676, answer score: 5