How to get an errorlevel in a for /f loop from the command result?

Question

I'm running an SQL request in batch-file and I need to get the errorlevel as well as the output it gives. I've tried using :

for /f "tokens=*" %%i in ('%SQLCommand%') do ( ...

and in the 'do', if the errorlevel is 1, then I can do whatever I plan to do, but I can't seem to find a way to get the errorlevel from the result of the command. I'm also not sure if the errorlevel can change while in the loop, but that I was planning on testing it as so I would get a way to know if the command change the errorlevel.

Malcom

the ('command') is executed in a separate process. This should answer your question. (Hint: errorlevel is lost, when the process ends) – Coo

@Coo Oh! Then i'm guessing i really can't control this other process to return any value. – Malcom

correct (with a for loop). Alternative: %SQLCommand% > output.txt and if %errorlevel% == 1 for /f "tokens=*" %%i in (output.txt) do ( ... – Coo

@Coo Yeah, that was the "Plan B" i was going for it wasn't possible. Guess I'll be going with it! Thank you! – Malcom

for /F %%L in ('%SQLCommand% ^& call echo %%^^ErrorLevel%%') do ( ... ) could perhaps be used to capture the ErrorLevel as well (although I'm not sure whether I appied escaping correctly as I can't test right now)... – Guideline

Answer 1

The problem here is that the command line assigned to environment variable SQLCommand is executed by FOR in a separate command process in background with cmd.exe /C and its output to handle STDOUT is captured by FOR and processed line by line. For that reason the exit code of the SQL command line cannot be evaluated in command process executing the batch file.

The most likely best solution is running the SQL command line in current command process with redirecting the output of the SQL command line into a temporary file, evaluating the exit code and processing the output according to exit code.

The batch file below demonstrates this solution. It can be started without an argument, with a valid file/folder name as first argument, or with an invalid file/folder name for watching error handling.

@echo off
setlocal EnableExtensions DisableDelayedExpansion
set "OutputFile=%TEMP%\%~n0.tmp"

if "%~1" == "" ( set "DirArg=%TEMP%" ) else set "DirArg=%~1"
set "SQLCommand=dir /A /B "%DirArg%""

%SQLCommand% >"%OutputFile%" 2>nul
echo/

if errorlevel 1 (
    echo An error occurred on execution of command:
    echo/
    echo %SQLCommand%
    echo/
    echo Exit code is: %ERRORLEVEL%
    goto EndBatch
)

echo Execution of command was successful. The output is:
echo/

for /F "usebackq delims=" %%I in ("%OutputFile%") do echo %%I

:EndBatch
del "%OutputFile%"
endlocal
echo/
pause

For understanding the used commands and how they work, open a command prompt window, execute there the following commands, and read entirely all help pages displayed for each command very carefully.

• del /?
• echo /?
• endlocal /?
• for /?
• goto /?
• if /?
• pause /?
• set /?
• setlocal /?

See also the Stack Overflow questions:

• What are the ERRORLEVEL values set by internal cmd.exe commands?
• Which cmd.exe internal commands clear the ERRORLEVEL to 0 upon success?

Cagle

Answer 2

It is possible to get the %ERRORLEVEL% with for /f, as aschipfl pointed it out in the comments, but it is tricky, I found this on dostips.com forum and now I get the error number from the command so using that code as base I did something like this:

@echo off
setlocal EnableDelayedExpansion

for /f "tokens=*" %%i in ('%command% ^|^| call echo ERROR_NUMBER%%^^^^errorlevel%%') do (
    set OutputLine=%%i
    if /i "!OutputLine:~0,12!" == "ERROR_NUMBER" (
        set ERRNB=!OutputLine:ERROR_NUMBER=!
        echo The error number was: !ERRNB!
    )
)

Note that I'm using EnableDelayedExpansion, the %command% it's just the command that will be executed, ^|^| to escape the pipes characters and is executed when the previous command fails and the rest is a way to get the error number in %%^^^^errorlevel%% in the dostips.com forum Dave Benham says it's just a hack:

It is not really escaping anything. It is a hack that postpones the expansion of the variable.

Remember that in a command line context, the result of %DoesNotExist% is %DoesNotExist%. Also remember that a caret before any character results in that character being preserved. It doesn't have to be a special character.

The caret is treated as part of the variable name during the expansion phase. So lets say we have a variable named "myVar". Then a command line statement like call echo %^myVar%, tries to expand the variable "%^myVar%" before the CALL, but it doesn't exist. The caret is then removed (the m is preserved), and you are left with %myVar%. Then after the CALL it tries again and properly expands the properly named variable.

Of course the above will fail if a variable named "^myVar" exists. But carets are not normally used in variable names.

Dave Benham

Forswear