sql server - Weird behavior of Try-Catch in a Stored Procedure -
I have a stored procedure where I need some kind of, but I do not know if the artist will succeed. In a compulsory language, I will use some type of "TryCast" pattern I thought it would be equivalent to T-SQL:
Try Cast (some type of Try some value in the form of the end) Finally try to catch the end of the catch On the surface, it looks equal. If @SomeTypeVar does not begin and the cast fails, then I get the NULL to work with it; The true value is if the artist succeeds.
I have used this code in a stored procedure, but this results in an error: message 2812, level 16, state 62, line 20. Current transaction can not be committed and Operation log file that you write can not support it. Roll back the transaction. Some research has led me to this question on stack overflow and other times when try-catch fails in T-SQL:
- Alerts or informational messages which have a severity of 10 or less
- Errors whose intensity is 20 or more That is, the SQL Server Database Engine for session suspends the work processing Is obtained. If an error occurs that has 20 or more intensity and the database connection is not interrupted, then ... captures the catch error
- Attendance, such as client-interrupt request or broken client connection.
- When the system is finished by the administrator, using the KILL statement.
The following types of errors are not operated by the CATCH block when they are at the level of execution of TRY ... Catch creation:
- Compile errors, such as syntax errors, which prevent batch from running.
- Errors that occur during statement-level re-combinations, such as object name resolution errors that occur after the compilation of suspended name resolution
These errors batch, The collected procedures or triggers are returned to the level running. First of all, I thought I fell into the statement- the real recompilation bucket (as my error level is 16) until I tried to biscuit the problem, the minimum reproduction is thus :
The process of making the process fails. As soon as the indexes begin, try to cast (some sort of @someValue) to try to get started, finally the end of the catch-end Tryand calling code:
starts tran exec fails indentation committanceThis generates the above error which I Was discussed. However, I remember that if there is no parameter in any stored procedure, you can call it without exec. It succeeds with:
launches trance
. Order (s) were successfully completedFurther experiments let me start trying to select one with another error level 16:1/0 end try catching catchWhich works in both cases, does not produce any line of production. / P>
I have two questions:
- Why is a different behavior without calling
execand without process?- Why does the second error of the same error level increase after the catch?
Exchange keyword is optional only if this is the first statement in the batch that is not related to this parameter It is necessary in all other references that Microsoft has inherited this strange behavior with the Sybase code base as well as many other T-SQL Parsing rules. I suggest that you follow a strict T-SQL coding style to avoid goucease.
The code below is running without any errors because it is not executing any one proc. Since no semicolon details are terminator, the name of the stored procedure becomes part of the BEGIN TRAN statement and it has been interpreted as the transaction name.
The start tray fails You will receive the expected syntax error during compiling if you add the statement terminator and it specifies you to EXEC The path will take down.
Start Tran; EXEC fails; Committed; Keep in mind that Statement Terminator is not used, so I suggest that you will be in the habit of specifying them. See.
Comments
Post a Comment