CODEWITHSUNDEEP
batch-filesqlcmd
Asad
Asad

Reputation: 73

How do I break my For loop if an error is returned

I have to break sqlcmd batch command when any error occur in .sql scripts in a folder of one or more .sql script files.

for %%G in (*.sql) do sqlcmd -b /S "ServerInstance" /d "DBName" -U "Username" -P "Password" -i "%%G"
IF %ERRORLEVEL% ==1 EXIT /b

This command executes all files in folder one by one (in sequence) I have two .sql script files in my folder; first script return error in execution thats where I want to break the execution, but it does not break and continue execution of second script in the folder.

Upvotes: 0

Views: 376

Answers (1)

aschipfl
aschipfl

Reputation: 34919

Since you have the if %ErrorLevel%==1 query after the loop, this is of course not exited.

Moving it into the loop would solve your problem, although I would not check ErrorLevel for 1, I would rather check it against not being 0:

for %%G in (*.sql) do (
    sqlcmd -b /S "ServerInstance" /d "DBName" -U "Username" -P "Password" -i "%%~G"
    if not ErrorLevel 1 exit /B
)

This would exit the loop if an ErrorLevel value of 1 or greater is encountered, so it is expected that your command does not produce a negative value. (A nice side effect of this syntax is that it does not require delayed variable expansion, which would be required for a condition like if !ErrorLevel! neq 0.)

An even better and simpler approach is to use the conditional execution operator ||, which checks the exit code of the preceding command (which is in most cases the same as ErrorLevel) and executes the following one only in case of a non-zero value:

for %%G in (*.sql) do (
    sqlcmd -b /S "ServerInstance" /d "DBName" -U "Username" -P "Password" -i "%%~G" || exit /B
)

Upvotes: 1

Related Questions