Search code examples
sql-serverbatch-filesqlcmd

Using a condition in a batch script with SQL

I'm trying to set up a batch script that basically runs a SQL statement against a database, and if the script returns results it will follow some logic.

Is there a way to have SQLCMD actually return the number of rows it found, or something similar?

I see that I can have the output displayed on the screen or a file, but is there a way to have it put it into a variable so I can have the script evaluate the variable? For example:

SQLCMD -q "select count(*) from active_connections" -r @varactive
IF @varactive > 0 THEN
    <do things>
ELSE END

Or would I need to switch to Powershell to handle this sort of logic?

Solution

  • While @Gary is technically correct that the only thing returned is the ERRORLEVEL, sqlcmd does also display its results to STDOUT. Armed with that, you could do something like this in a batch file:

    set SERVERNAME=yoursqlserver
for /f "skip=2" %%x in ('sqlcmd -S %SERVERNAME% -Q "select count(*) from active_connections" ^| findstr /v /c:"rows affected"') do set COUNT=%%x
echo There are %COUNT% records in the active_connections table.