Pass parameter to SQL query in a file

Question

I have the following query (call it queryA):

Select * from TableA where FieldA in ('foo', 'bar')

I would like to write a (shell?) script file something like:

execute queryA take parameter values from ParamFileA write output to OutputFileA

Where ParamFileA would contain ('foo', 'bar')

Is it possible? This is for SQL Server.

Did you try something? It looks similiar to [this](http://stackoverflow.com/questions/33010612/tsql-string-modify) — Lukasz Szozda, Oct 08 '15 at 17:23
@lad2025 Thank you, it looks like your link is about manipulating format of parameters, I am curious how to pass parameters from the file. — user1700890, Oct 08 '15 at 17:26
Which shell PowerShell/CMD? It is as simple as build query + read file + concatenate to query values for in + execute it — Lukasz Szozda, Oct 08 '15 at 17:27
What have you tried so far? Did you took a look at sqlcmd? https://msdn.microsoft.com/en-us/library/ms170572.aspx — Ricardo Pontual, Oct 08 '15 at 17:28
For really simple things doing this with .bat & sqlcmd might work, but for anything that requires more complex parameters, output formatting or error handling I would use a script language. — James Z, Oct 08 '15 at 17:31
In case of sqlcmd, how do I pass a file which contains list of parameters? — user1700890, Oct 08 '15 at 17:40
See my answer, I think it will at least get you started. But really, you'd be better off using PowerShell, VBScript, Python, something like that instead. Windows bat files are only for people like me, operating in environments where getting new software installed is difficult and not all script users have admin rights. If you can use sharper tools for this I'd recommend it. — Caleb Mauer, Oct 08 '15 at 22:56

score 1 · Accepted Answer · answered Oct 08 '15 at 22:50

Make a .bat file with the following content (read the REM comments for details):

REM This script expects these parameters: Path of file with query to run, path of file with parameters, path of file to save output in.
set SQLFILE=%1
set PARAMFILE=%2
SET OUTPUTFILE=%3

SET TEMPFILEPATH=C:\Temp\tempsqlstatementt.sql

REM Combine SQL command and parameters into one temporary file.
copy /B %SQLFILE% + %PARAMFILE% %TEMPFILEPATH%

REM Execute combined SQL command and write to specified output
sqlcmd -S myServer\instanceName -i C:\Temp\tempsqlstatementt.sql -o %OUTPUTFILE%

REM Cleanup the temp file.
DEL %TEMPFILEPATH% /F /Q

You can call this bat file like this:

ExecuteWithParams QueryFile ParamFile OutputFile

Here it is again but I put in actual file paths:

C:\Users\You\>ExecuteWithParams.bat QueryFile.sql ParamFile.sql OutputFile.txt

Also, if sqlcmd isn't on the path you'll have to put the path to sqlcmd into the bat file.

Pass parameter to SQL query in a file

1 Answers1