How to use IF statement in sqlcmd

1.2k views Asked by At

If there is an error while running the procedure then echo 'error' else 'success'

sqlcmd -S DANAA-LAPTOP -d NewsWebsite -q "SP_DictionaryMain 'NewsWebsite','NEW'"  
       -o C:\logFile\ReportExE-%date:~-4,4%%date:~-10,2%%date:~-7,2%.txt 
1

There are 1 answers

0
elzooilogico On BEST ANSWER

try cmd && and || operators, where && means previous command succesful and || means previous command failed.

as in

mycommand && echo success || echo error

you may also write compound commands within parens

mycommand && (set "somevar=ok" & echo success) || (set "somevar=ko" & echo error) 

or, if you are running a batch script

mycommand && (
  set "somevar=ok" 
  echo success
) || (
  set "somevar=ko" 
  echo error
) 

Also, you need to use the -b switch to force sqlcmd return an ERRORLEVEL that may be captured within batch or command line. This switch also forces sqlcmd to close when an error occurs.

so, in your case

sqlcmd -b -S DANAA-LAPTOP -d NewsWebsite -q "SP_DictionaryMain 'NewsWebsite','NEW'"  -o C:\logFile\ReportExE-%date:~-4,4%%date:~-10,2%%date:~-7,2%.txt 2>NUL && echo success || echo error

As you want to echo something, error output is suppressed with 2>NUL. Remove it to see error messages from sqlcmd