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.