Yuri55 (5/26/2013)
Moreover, as all statements were executed successfully, how SP can return error-it suppose to fail on any if there is syntax error?
Because the syntax error was in the dynamic SQL, hence SQL can't see it when it parses the procedure, only when that piece of dynamic SQL executes. The syntax error will cause that piece of dynamic SQL to fail (with the error), the rest of the procedure will carry on running.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability