Thursday, 5 March 2015

How can SQL code behave differently in or outside procedure and about poor error handling

During refactoring of very old code, I found a ancient/ugly and in the end even totally wrong error handling script. Interesting is that the developer probably tested it outside of procedure and it did "kind a work" for him, ... :)

I'll add details later (or much later).
-- 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.
-- 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

No comments:

Post a Comment