SQL Server sqlcmd setvar depending of selected database

Question

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.

Answer 1

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.

• Solomon Rutzky: Yeah, I remember seeing some of these tricks on a few blog posts. You can definitely do some interesting things, but I would mention that at least some of the SQLCMD commands are only available to SQLCMD.EXE and will not work in SQLCMD mode in SSMS. I thought that :out was one of them which is why I didn't even bother mentioning it, but I just tested and it does work in SSMS so I think your approach could work, even if we both advise against it ;-). Also, I am 112% in favor of re-runnable / idempotent scripts to move code to any environment :). But +1 since this does seem to work.
• Benoit: Tanks for the advise, but this is only to avoid error (like aply dev script to prod). I test your solution then accept it.

Answer 2

While it is possible to do some trickery to get SQLCMD commands to do some interesting things, they are not generally meant to facilitate logic.

And, what you are trying to accomplish with conditional SQLCMD is possibly not in your best interest either. You are trying to create a release script that works differently for different environments. You would be better off creating a release script for "Dev" and another for "Prod" so that any other considerations can be accounted for easily.

However, with that caution being said, you might be able to handle some amount of dynamicness by using environment variables. Meaning, you can set relativepath as an environment variable before SQLCMD is called instead of using :setvar inside of the script:

SET TestVar=D:\Project1\Dev\Sql
sqlcmd -S (local) -E -W -Q "PRINT '$(TestVar)';"