Setting sqlcmd variable using tsql variable

Question

It seems like this approach for setting sqlcmd variable does not work so I am wondering if there is another way?

DECLARE @d varchar(max);
SET @d = 'foobar';

:setvar database @d
USE $(database)

Note: I realized that using :setvar in this manner works but I want to set it using a variable in the T-SQL script instead of explicitly setting it.

:setvar database "foobar"

Jeroen Mostert · Accepted Answer · 2015-02-23 22:57:50Z

1

This makes no logical sense, from the perspective of SQLCMD. :setvar is a command that lives outside the T-SQL script; it can't "see" anything the T-SQL script does at runtime. You can set the variable through any number of means, including environment variables, options and :setvar itself, but you can't mix in runtime T-SQL execution.

Rethink your script so the SQLCMD script lives "outside" the T-SQL script, and the T-SQL script does the necessary runtime actions. Since you can execute dynamic statements in T-SQL, there shouldn't be any need for setting an SQLCMD variable through T-SQL rather than the other way around.

answered Feb 23, 2015 at 22:57

Jeroen Mostert

29k2 gold badges60 silver badges92 bronze badges

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

Collectives™ on Stack Overflow

Setting sqlcmd variable using tsql variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related