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.

Solution 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%

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

Solution 3

The answer from @GerardoLima was 90% of the way there. You additionally need to evaluate the input value with set /a to strip the leading whitespace.

This is not required with the answer from @Christian.K

SQLCMD -E -S devserver -Q "SET NOCOUNT ON; SELECT COUNT(1) FROM Cases" -h -1 > result.txt
set /p raw=< result.txt
echo '%raw%'
set /a value=%raw%
echo '%value%'

My output was

'          0'
'0'

Comments

[Christian.K]

+1 Just saw, after posting my answer that your's also includes the -h -1 option.

[Dan]

Partly ... how do I get the value back into the DOS batch file ?

[Gerardo Lima]

Then maybe @Christian.K solution (using FOR) might suits you better, since it will evaluate the result of the command and then you can assign to a batch variable, you only have to add a simple test to avoid the empty string.

[Dan]

I've tracked down a solution and added to your post ... thanks for the response

[JJS]

reading the value from the file with set /p value=<result.txt is great.

[John Evans Solachuk]

Sometimes, there might be leading or trailing spaces in the result so you might want to use for /F "delims= " %%i... instead. Do note that this deletes all spaces including the ones in between so be careful.

[Christian.K]

@Mark OK, I see. In that case it might be better to trim the spaces off the count variable, after the for look: set count=%count: =%.