sqlcmd mode from SSMS - setvar assignment to a sub

Question

I'm running this from SSMS 2008R2 with SQLCMD Mode enabled.

With the code below:

:SETVAR GREETING "HELLO"
:SETVAR SALUTATION $(GREETING)

SELECT '$(GREETING)'
SELECT '$(SALUTATION)'

I see the following in the results:

HELLO
$(GREETING)

I was hoping to see:

HELLO
HELLO

Is there a way to have a scripting variable in the assignment of another scripting variable? If so, what is the syntax?

Answer 1

You can also do the opposite if you need to:

DECLARE @firstVar VARCHAR(30) = 'theUltimateValue';

:setvar secondValue @firstVar

select $(secondValue) as whatIReallyWant

Returns

whatIReallyWant
------------------------------
theUltimateValue

Answer 2

the variable substitution in :SETVAR doesn't work, because in this case it's just a token substitution.

You can simulate the behaviour you expect as this:

:SETVAR GREETING "HELLO"

DECLARE @salutation VARCHAR(5) = $(GREETING);
SELECT @salutation;

Regards.