SQLCMD using batch variable in query

Question

If I'm using sqlcmd in a batch script, can I reference a batch variable inside the query?

So that if I do something like

for /f "delims= " %%a in ('sqlcmd -S SERVER -d DATABASE -Q 
    "SELECT Column1 FROM Table1 WHERE Column1=[specific number]"') do set var1=%%a

how could I do something like this?:

for /f "delims= " %%b in ('sqlcmd -S SERVER -d DATABASE -Q
    "SELECT Column2 FROM Table1 WHERE Column2=[var1]"' do set var2=%%b

So that the WHERE condition in the second sqlcmd statement is using the variable set in the first statement?

Alex Mitelman · Accepted Answer · 2015-08-26 15:53:03Z

0

Change Column2=[var1] to Column2=%var1%

answered Aug 26, 2015 at 15:53

Alex Mitelman

1013 bronze badges

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

5 Comments

JohnN Over a year ago

Okay so I can still use the same syntax indiscriminately?

Alex Mitelman Over a year ago

The %var1% will be replaced by real value during batch execution.

Alex Mitelman Over a year ago

This batch has some issues you need to be aware. 1). Select may return multiple records. Last value will be assigned to the variable. SELECT TOP 1 may help. 2). Use SET NOCOUNT ON; before the statement, othervise line "(1 rows affected)" will be the last line. 3). If SELECT affect 0 records then last line will be "-----------" an that will be assigned to the variable.

JohnN Over a year ago

Ah, so it won't return null if there are no results?

Alex Mitelman Over a year ago

No. SQLCMD will not return NULL. It returns message "(0 row(s) affected)"

user2955677 · Accepted Answer · 2015-08-26 15:55:13Z

0

You can pass arguments from the environment to sqlcmd by using the -v switch and referencing the parameters in your sql statement by the $(variable) syntax. E.g. in your second statement :

for /f "delims= " %%b in ('sqlcmd -S SERVER -d DATABASE -Q
    "SELECT Column2 FROM Table1 WHERE Column2=$(var1)" -v var1=%var1%' do set var2=%%b

answered Aug 26, 2015 at 15:55

user2955677

564 bronze badges

2 Comments

JohnN Over a year ago

Are the parenthesis around var1 i.e. (var1) part of syntax or just implying a placeholder?

user2955677 Over a year ago

They are indeed, the syntax is $(externalVariableName)

Collectives™ on Stack Overflow

SQLCMD using batch variable in query

2 Answers 2

5 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related