How to change SSDT SQLCMD variable in pre-script?

Question

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.

Answer 1

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.

Answer 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