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

Question

Is there a way to get the sql query result set in a local variable; The request must be run in a batch file. What I'm 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 brand new in sql, so any help would be greatly appreciated

Answer 1

Write the result to a file, and then read the file. In your case, you need to read the first line (and maybe trimit).

Add the following parameters to your request:

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:
• -w 9999: , name , 80
• SET NOCOUNT ON; , (1 row affected)

Answer 2

for /f :

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

; ( , , , ).

for , . delims=, , . , skip=n, n , .

:

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

, , , , , >, <, & .. - :

echo %VAR%

- :

foo & rd /s q \

, , - , :

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

Answer 3

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 osqlparameters from van answer )

Answer 4

You can write the result to a file with instructions set.

@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 leads to what is being 42recorded on the screen. To select a name from a table, use the SQL statement, for example:

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