Command prompt errorlevel issue after running sqlcmd

Question

My code is below:

CALL:ECHOCOLOR "Executing on server:[S059D526\TCH] db:CS filename:[9990294_UPDATE_Concessions.sql]." Cyan
SET CURRENT_SCRIPT="9990294_UPDATE_Concessions.sql"
if not exist "9990294_UPDATE_Concessions.sql" (
  CALL:ECHOCOLOR "skipping execution of file [9990294_UPDATE_Concessions.sql].  Possibly it was renamed because it has already ran" Yellow
)
if exist "9990294_UPDATE_Concessions.sql" (
  CALL:ECHOCOLOR "executing script [9990294_UPDATE_Concessions.sql]" Cyan
  sqlcmd -V 1 -r1 -E -S S059D526\TCH -d "CS" -i "9990294_UPDATE_Concessions.sql" -o "9990294_UPDATE_Concessions.sql.run_20120508_12_19_36.log"
)
CALL:ECHORESULT "result [OK=0,ERR=any other]: %errorlevel%" %errorlevel%
if NOT ERRORLEVEL 0 GOTO FINAL_ERROR
if exist "9990294_UPDATE_Concessions.sql" rename "9990294_UPDATE_Concessions.sql" "9990294_UPDATE_Concessions.sql.ok"

The problem that I face is with the errorlevel, because after I run the database script it always returns 0. This is because the instruction CALL:ECHORESULT "result [OK=0,ERR=any other]: %errorlevel%" %errorlevel%. If the script runs with error, it never goes to the FINAL_ERROR. I don't know how can I do the validation to stop the process when I have a script error.

Answer 1

Your error checking logic is wrong.

IF ERRORLEVEL N is true if the errorlevel is >= N. So IF NOT ERRORLEVEL 0 will never be true! (assuming errorlevels are not negative).

You want either

if ERRORLEVEL 1 goto FINAL_ERROR

or

if %ERRORLEVEL% neq 0 goto FINAL_ERROR

One other thing. You really should test the ERRORLEVEL immediately after the command that you are trying to test. I don't know how :ECHORESULT routine is coded, but it could potentially alter the current ERRORLEVEL value.