CODEWITHSUNDEEP
t-sqlbatch-fileuser-inputsqlcmd
KD84
KD84

Reputation: 15

How to use a bat file to take data from user and query sqlserver DB

I'm struggling to get this right. I've searched online and can't find the correct solution. I want to write a bat file to place on a users desktop that when clicked will ask the user to enter a serial number and use that serial to query the database

database is SQL Server 2012

So far I have a bat file (batfile.bat)

batfile.bat contains

sqlcmd -S SERVER1\MSSQL2012 -i C:\KD\SerialNumber.sql -o C:\KD\SerialNumber.txt

In SerialNumber.sql I have

USE StockDatabase

GO

select * FROM tblStock where SerialNumber like '%1234%'

GO

But obviously this doesn't prompt the user for a serial Or use the input in the sql What I want to do is use the Serial Number inputted by the user in the SQL query

I guess i should do something like

set /P var= Enter Serial

to get the user input? But how do I use what they have input in the SQL query?

I've changed batfile.bat to;

ECHO OFF

set /P var= Enter Serial: %=%

Serial %var% > SerialNumber.sql

sqlcmd -S EUROSERVER2\MSSQL2012 -i C:\KD\SerialNumber.sql -o C:\KD\SerialNumber.txt

and the sql to 

select * FROM tblStock where SerialNumber like '%1234%

thinking it made sense but it doesnt work

Upvotes: 0

Views: 2798

Answers (1)

ssarabando
ssarabando

Reputation: 3517

To pass an environment variable's value to your SQL script you can simply refer to it by name inside the script (just surround the environment variable's name with $()) or, if you prefer to set it explicitly, use the -v switch of sqlcmd.

For example (explicit use of the variable):

set /P sn= Enter Serial: 
sqlcmd -S SERVER1\MSSQL2012 -i SerialNumber.sql -o SerialNumber.txt -v serial=%sn%

Or (implicit use of the variable):

set /P serial= Enter Serial: 
sqlcmd -S SERVER1\MSSQL2012 -i SerialNumber.sql -o SerialNumber.txt

And, for it to work, your script (SerialNumber.sql) would have:

select * FROM tblStock where SerialNumber like '%$(serial)%'

More information here (MSDN).

Upvotes: 2

Related Questions