Accessing the returned value of a SQL statement in SQLCMD

Question

I'm trying to get the value of a SQL statement when I run it in a DOS batch file ...

sqlcmd -E -S DEVSERVER -Q "SELECT  COUNT(1) as [CaseCount] FROM Cases"

I'm not after the error level as in this stackoverflow question, rather I'm after the actual count returned from the database, so I can do some additional logic.

Answer 1

You can easily save the result of the execution into a text file, either by using the -o sqlcmd flag or by using the standard > redirector. You can then format this file by removing the column header (flag -h) and removing the rowcount from SQL Server (SET NOCOUNT ON).

The following script will generate a file result.txt with only the value of COUNT(1) and a line break:

SQLCMD -E -S devserver -Q "SET NOCOUNT ON; SELECT COUNT(1) FROM Cases" -h -1 
  > result.txt

And then read the value back with ...

set /p value=< result.txt
echo %value%

Answer 2

You can avoid an extra/temporary file, by calling sqlcmd.exe using the FOR batch command:

for /F usebackq %%i in (`sqlcmd -E -S "devserver,4711" -h-1 -Q "SET NOCOUNT ON; SELECT COUNT(1) ..."`) do (
    set count=%%i
)

if not defined count (
    echo Failed to execute SQL statement 1>&2
) else (
    echo %count%
)

Some notes:

• If called from an interactive CMD.EXE session, i.e. the command line, use %i instead of %%i
• The -h-1 option tells sqlcmd.exe to suppress the column headers, so the output is really only one line of text.
• I used a fictional port 4711 with the server, to show that you need to put the whole "server,port" specification in double quotes. Otherwise, due to CMD's command line parsing rules, sqlcmd would see server and port as to distinct arguments and fail.