Last active
August 29, 2015 14:16
-
-
Save papsl/fe4186e1339793cd0d36 to your computer and use it in GitHub Desktop.
Example Improper error handling with SQL try catch
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- This procedure will fail twice, first time in main TRY block and second time in CATCH statement because of converting ERROR_MESSAGE to INT | |
CREATE PROCEDURE dbo.SomeWork | |
AS | |
BEGIN TRY | |
-- let's do something | |
-- ... | |
-- Exception happens | |
PRINT 0/0 | |
END TRY | |
BEGIN CATCH | |
DECLARE @ErrorMessage VARCHAR(4000); | |
DECLARE @ErrorSeverity INT; | |
DECLARE @ErrorState INT; | |
SELECT | |
@ErrorMessage = ERROR_MESSAGE() + ' Procedure '+ ERROR_PROCEDURE() + ' Line: '+ERROR_LINE(), | |
@ErrorState = ERROR_STATE(); | |
RAISERROR (@ErrorMessage,16,@ErrorState); | |
END CATCH | |
GO | |
EXEC dbo.SomeWork | |
-- Output: | |
-- Msg 245, Level 16, State 1, Procedure SomeWork, Line 14 | |
-- Conversion failed when converting the nvarchar value 'Divide by zero error encountered. Procedure SomeWork Line: ' to data type int. |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Running same code out of procedure will work (read: it will fail only once), because ERROR_PROCEDURE is NULL and entire evaluation of @ErrorMessage will result in NULL | |
BEGIN TRY | |
-- let's do something | |
-- ... | |
-- Exception happens | |
PRINT 0/0 | |
END TRY | |
BEGIN CATCH | |
DECLARE @ErrorMessage VARCHAR(4000); | |
DECLARE @ErrorSeverity INT; | |
DECLARE @ErrorState INT; | |
SELECT | |
@ErrorMessage = ERROR_MESSAGE() + ' Procedure '+ ERROR_PROCEDURE() + ' Line: '+ERROR_LINE(), | |
@ErrorState = ERROR_STATE(); | |
RAISERROR (@ErrorMessage,16,@ErrorState); | |
END CATCH | |
-- Output | |
-- Msg 50000, Level 16, State 1, Line 16 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
http://paps-tech.blogspot.com/2015/03/sql-code-can-behave-differently-in.html