Weird CMD batch script reporting of %errorlevel%

Question

In the script below, when there is a sql error, %errorlevel% is 0 - why is this?

IF %RELEASE% GTR 2 (
    (
    echo WHENEVER SQLERROR EXIT FAILURE
    echo @release.sql
    echo exit
    ) | sqlplus x/x@orcl
    echo error: %errorlevel%
    if %errorlevel% gtr 0 goto dberror
)   

If I remove the if block (below) then %errorlevel% is NON zero! Why does the if statement affect the error level?

    (
    echo WHENEVER SQLERROR EXIT FAILURE
    echo @release.sql
    echo exit
    ) | sqlplus x/x@orcl
    echo error: %errorlevel%
    if %errorlevel% gtr 0 goto dberror

---

Update: Believe it was the way I was testing for an error. I think instead of:

if %errorlevel% gtr 0 goto dberror

.. should be using:

if errorlevel 1 goto dberror

Useful link here

Answer 1

Sigh. It's all to do with the horrible dos parsing, and the point at which cmd replaces %errorlevel% with its value. When you enclose commands with (...), cmd first reads in those commands as though they were all written on one line, expanding variables as it goes. Thus in your first code block, %errorlevel% is replaced by its value when the block is parsed. It's as if you wrote:

IF 3 GTR 2 (
   (
   echo WHENEVER SQLERROR EXIT FAILURE
   echo @release.sql
   echo exit
   ) | sqlplus x/x@orcl
   echo error: 0
   if 0 gtr 0 goto dberror
)

(assuming $RELEASE was 3). The reason your if errorlevel fix works is that cmd does not do any (too) early variable expansion on that re-formulation. You can use call to avoid this problem:

IF %RELEASE% GTR 2 (
    (
    echo WHENEVER SQLERROR EXIT FAILURE
    echo @release.sql
    echo exit
    ) | sqlplus x/x@orcl
    call echo error: %%errorlevel%%
    call if %%errorlevel%% gtr 0 goto dberror
)

(which I think is clearer than startlocal ENABLEDELAYEDEXPANSION and !errorlevel!—YMMV of course).