3

I am trying to find out how to change SQLCMD variable on the fly and I couldn't get it working.

The goal is to get value from the SELECT and assign SQLCMD variable with that value.

I've tried:

1)

:servar myVariable 
SELECT @myVariable = 1

2) Tried to put the value of the file with :OUT but it says that:

Error 1 72006: Fatal scripting error: Command Out is not supported.

1

3 Answers 3

4

This isn't possible. SQLCMD is just a pre-processor before the script is even sent to the server.

The answer you accepted is somewhat confusing sleight of hand.

It doesn't actually assign to the SQLCMD variable on the fly in any meaningful way.

:setvar myVar @sqlVar

Just assigns the string "@sqlVar" to the SQL Cmd variable (unnecessarily and misleadingly twice). This string then gets used in the string replacement to $(myVar).

All of this happens before the script is even sent to the server (and so obviously before execution has begun and the SQL variable is assigned any value)

The result of the script after replacing all $(myVar) with @sqlVar is as below.

This is what is what is sent to the server.

DECLARE @sqlVar CHAR(1)

SELECT @sqlVar = '1'
SELECT @sqlVar as value

SELECT @sqlVar = '2'
SELECT @sqlVar as value

There is no on the fly assignment to the SQLCMD variable at all. The only assignment is to the SQL variable.

Sign up to request clarification or add additional context in comments.

Comments

2

You need to declare a temporary sql @variable and assign it value from select.

Then initialize sqlcmd variable using sql @variable.

DECLARE @sqlVar CHAR(1)

SELECT @sqlVar = '1'
:setvar myVar @sqlVar
SELECT $(myVar) as value

SELECT @sqlVar = '2'
:setvar myVar @sqlVar
SELECT $(myVar) as value

2 Comments

There is no initialization of the sqlcmd variable from the SQL variable going on here.
I'm not sure why this answer is accepted. It doesn't work as soon as a soon as a batch terminator is added.
0

Answer to https://stackoverflow.com/a/30086248/623190:

Cool! Be just aware of a pitfall:

DECLARE @sqlVar CHAR(1)

SELECT @sqlVar = '1'
:setvar myVar @sqlVar
SELECT $(myVar) as value

GO
-- SELECT @sqlVar = '2'
-- :setvar myVar @sqlVar
SELECT $(myVar) as value

result:

Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@sqlVar".

If we cannot bring a value of SQLCMD Variable over GO (when it was assigned by regular variable) then there is no sense in using of the SQLCMD Variable within a batch.

Compare with the code that works:

:setvar myVar '1'
SELECT $(myVar) as value
GO
SELECT $(myVar) as value

And this magically works:

:OUT C:\Temp\SetVarTest.sql
declare @command varchar(100) = ':SETVAR myVar ' + cast((select count(*) from sys.objects) as varchar(10)); 
PRINT @command
GO
:OUT stdout
:r C:\Temp\SetVarTest.sql
GO
PRINT $(myVar) 

See more in Hacking SQLCMD Mode article.

2 Comments

did you try that in SSDT while publishing? or just enabled SQLCMD mode in SSMT? It is obvious that temp variable is gone after GO
You are right! I tested it in SSMT only. I will check in SSDT. Thanks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.