Using batch variable in sqlcmd query

Question

I'm trying to create a batch to execute a few sql script and some queries. I use sqlcmd in my batch like this :

@echo off
sqlcmd -S server -U user -P password -Q query

As I will execute different queries on the same server, I'd like to replace the server statement with a variable. So I could write something like this :

@echo off
SET server=192.X.X.X
sqlcmd -S server -U user -P password -Q first query
sqlcmd -S server -U user -P password -Q second query

I found this question on SO but I'm still unable to understand how that works :

SQLCMD using batch variable in query

Does anyone have an idea ?

Thanks a lot!

Oh gosh, I tried anything but this. I did it like <server> or %server but never like this. Thanks a lot man ! — Arhyaa
– Arhyaa, Commented Dec 21, 2017 at 16:18

Compo · Accepted Answer · 2017-12-21 16:24:33Z

2

Expanding on my comment a little:

@Echo off
Set "server=192.X.X.X"
Set "user=myname"
Set "password=pa55w0rd"
sqlcmd -S %server% -U %user% -P %password% -Q first query
sqlcmd -S %server% -U %user% -P %password% -Q second query

You may wish to enclose your variables with relevant quoting as necessary.

answered Dec 21, 2017 at 16:24

Compo

39k5 gold badges32 silver badges46 bronze badges

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

2 Comments

Arhyaa Over a year ago

And what if I'd like to concat a variable with a string in sqlcmd ? I tried with "sqlcmd -S %server%&"myString" but it's not functional. Is it possible directly in the sqlcmd statement or should I do this concatenation in a SET var=%server%&"myString" in ordre to use it later ?

Compo Over a year ago

If you want to join them then just use Set "var=%server% myString" (with or without the whitespace as needed). Additionally if you wish to include an ampersand, & then it will need escaping with the caret, ^ like this Set "var=%server% ^& myString".

Buffalo8 · Accepted Answer · 2017-12-21 16:17:28Z

0

From Microsoft: "Cmd.exe provides the batch parameter expansion variables %0 through %9. When you use batch parameters in a batch file, %0 is replaced by the batch file name, and %1 through %9 are replaced by the corresponding arguments that you type at the command line."

So if the number of queries you wish to execute is known just pass them in through parameters like this:

Test.bat:

@echo off
SET server=192.X.X.X
sqlcmd -S server -U user -P password -Q %1%
sqlcmd -S server -U user -P password -Q %2%

Then call Test.bat like this:

Test.bat "Select * from Test1" "Select * From Test2"

You can pass up to 9 queries this way.

answered Dec 21, 2017 at 16:17

Buffalo8

615 bronze badges

Collectives™ on Stack Overflow

Using batch variable in sqlcmd query

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related