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