How to set a local variable to the result of sql query in a batch file?

Question

Is there any way to retreive the result set of a sql query in a local variable; the query is to be run in a batch file. What I am trying to do is something like this:

set varname = osql -S%dstserver% -d%dstDB% -Q"SELECT name from table_name where Id = %siteId%" %osqluser% -b

varname is my local variable.

I am quite new in sql so any help would be greatly appreciated1

Answer 1

Write the result to the file and then read the file. In your case you need to read the first line (and possibly trim it).

Add following parameters to your query:

osql -S%dstserver% -d%dstDB% -Q"SET NOCOUNT ON;SELECT name from table_name where Id = %siteId%" %osqluser% -b -w 9999 -h-1 -o tempres.txt

• -o ...: output file (which you need to read later)
• -h-1: disable header
• -w 9999: to ensure that it handles correctly cases when your name is longer then default 80 characters
• SET NOCOUNT ON; before real query to disable the status string like (1 row affected)

Answer 2

You can write the result to a file containing set statements.

@echo off
osql -E -S servername -h-1 -Q "set nocount on; select 'set var=42'" > c:\set.bat
call c:\set.bat
echo %VAR%

This results in 42 being written to the screen. To select a name from a table, use a SQL statement like:

select 'set var=' + name from table_name where Id = %siteId%

Answer 3

You can use for /f to iterate over the output of a command:

for /f "usebackq delims=" %%x in (`your command`) do ...

You don't need a temporary file at all; it doesn't gain you anything here (except having to think about where you may have write permissions and remember to delete the file afterwards).

for iterates line-wise over the output, tokenizing as it goes. That's why there is a delims= at the end which effectively disables any tokenizing so you don't get your output split at spaces. There are other options, such as skip=n which will skip n lines before starting processing which you can use to ignore a header or so.

Inside that loop you can then do the following:

for /f "usebackq delims=" %%x in (`your command`) do set VAR=%%x

Be very careful what you do afterwards with that variable, though, as it may contain characters the shell treats as special, such as >, <, &, etc. You may create yourself here some sort of Batch Injection vulnerability when doing something like the following:

echo %VAR%

and someone decides to put the following string in his name:

foo & rd /s q \

If you know that only a single line with usable content returns and the rest is useless junk then you can break the loop prematurely:

for /f "usebackq delims=" %%x in (`your command`) do set VAR=%%x&goto break
:break

Answer 4

Use set /p:

osql -S%dstserver% -d%dstDB% -Q"SET NOCOUNT ON;SELECT name from table_name where Id = %siteId%" %osqluser% -b -w 9999 -h-1 -o tempres.txt  

set /p varname=<tempres.txt  

(Borrowing osql parameters from van's answer)