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
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).