How to conditionally exit .BAT file from SQL code run through SQLCMD

Question

I have a .bat (Windows command) file that includes invocations of SQLCMD and other commands. (Of course SQLCMD is sending my T-SQL code to SQL Server.) I want to detect certain conditions in the SQL code, and conditionally exit the entire batch file. I've tried various combinations of RAISERROR, THROW, and deliberate division by 0 (I'm not proud) along with various command line switches on SQLCMD and handling of errorlevel in the .bat file.

I tried the answer to 5789568 but could not get it to work in my case. Here are two files which show one failed attempt. It tries to abort if there are more than 3 tables. But it doesn't abort the bat file, as you can see when the final command (echo) executes. It doesn't even abort the run of SQLCMD, as you can see when it tells you how many tables there are.

example.bat

set ERRORLEVEL=0
sqlcmd -b -S dbread.vistaprint.net -E -d columbus -e -i example.sql
if %ERRORLEVEL% neq 0 exit /b %ERRORLEVEL%
echo we got to the end of the BAT file

example.sql

SET XACT_ABORT ON
if ((SELECT COUNT(*) FROM sys.tables) > 3)
    begin
    RAISERROR ('There are more than 3 tables.  We will try to stop', 18, -1)
    end
SELECT COUNT(*) FROM sys.tables

Answer 1

@dbenson's answer solves the problem with the .bat file. But there is also a problem with the .sql file, whose symptom is that the last line executes despite the error. Here is a complete solution. That is, these two files work correctly.

The "error" for which the code is checking is that a database named 'master' exists. If you replace 'master' with something that is not the name of a database, it runs without error.

example.bat

REM Replace the server name with any SQL Server server to which you
REM have at least read access.  The contents of the server don't matter.
sqlcmd -b -S dbread.vistaprint.net -E -i example.sql
if %errorlevel% neq 0 exit /b %errorlevel%
echo 'In the .bat file, we got past the error check, so run was successful.'

example.sql

if exists (select * from master.sys.databases where name = 'master')
    begin
    RAISERROR ('Found an error condition', 18, 10)
    return
    end
print 'In the SQL file, we got past the error check, so run was successful.'

Answer 2

%ERRORLEVEL% is not a normal environment variable. It is a dynamic pseudo variable that returns the current ERRORLEVEL. If you explicitly define a true environment variable with set ERRORLEVEL=0, then the dynamic nature is destroyed, and %ERRORLEVEL% will forever return the value of your user defined environment variable until you undefine your user value. You should never define your own values for ERRORLEVEL, RANDOM, CD, TIME, DATE etc.

If you want to clear the ERRORLEVEL, then you must execute a command that sets the value to 0. I like to use (call ).

Assuming there are no other problems with your code, it should be fixed with the following:

(call )
sqlcmd -b -S dbread.vistaprint.net -E -d columbus -e -i example.sql
if %ERRORLEVEL% neq 0 exit /b %ERRORLEVEL%
echo we got to the end of the BAT file

sqlcmd is an external command (exe program), so it always sets ERRORLEVEL. Therefore you should not have to clear the ERRORLEVEL, and (call ) could be removed. You really only have to worry about clearing the ERRORLEVEL before you run internal commands that do not set ERRORLEVEL to 0 upon success. (See Which cmd.exe internal commands clear the ERRORLEVEL to 0 upon success?)