Execute SQL from command line (CMD) with passing variable

Hai All,

Today I am going to share a small thing which may need for you in some situations, Today I am going to share how to call an sql query from a command line and also how to pass the variables to the query while calling the same from the Command Line.

Suppose my query is like below

SELECT * FROM tbl_name;

So write the same in a text or notepad file and save it as “MyScript.sql”, note that the extension should be “sql”

You can excute the query directly,but I am explaing this method to show how to pass variable into it

Suppose that you installed the SQL Server in a Folder like

“C:\Program Files\Microsoft SQL Server\”

then there you can find folder with 90 or 100 or anthing according to the version, open it

then in that you can go to “Tools\Binn”

there you can find a “SQLCMD.exe”by using that we are going to invoke our sql Query from Command line

write the following in a notpad or text file and save as “.bat”

cd c:\

cd /d "C:\Program Files\Microsoft SQL Server\90\Tools\Binn"

sqlcmd.exe -S .\SQLExpress -i "pathtoSQlscriptfle\databse.sql"

then run the bat file to excute the sql from Command line

here

-S means the server

-i means the input script

So that you can run the script

to pass a variable, you can use -v varName=”value”

in such case, You can access the variable in SQL Script as a bit Differ net way like

USE database_Name

DECLARE @Qury as NVARCHAR(MAX)

SET @Qury = 'SELECT * FROM tbl_name WHERE compareFiled = ''$(varName)'''

EXEC(@Qury)

here we are indirectly making a string of query with the passed variable “varName”

so we can call the bat file and the script will get executed.

Some additional options of SQLCMD.exe are

[-U login id]

[-P password]

[-S server]

[-d use database name]

[-l login timeout]

[-t query timeout]

[-q "cmdline query"]

[-Q "cmdline query" and exit]

[-o outputfile]

[-b On error batch abort]

you can find even more options by using “sqlcmd –?”

Thank you

Gibin Francis

Hi. I’m Software Engineer in India. I’m Web Developer, Creative Coder, Web Designer, logo Designer, Business Enthusiast, StartUp Enthusiast, Speaker, Writer and Designer. Inspired to make things looks better and Do the this in the best way.