March 8, 2016 at 10:10 am
We are currently researching this after we have already put this pattern in place for years. The issue is raiserror vs @err goto Return code. It has to do with the errors and reporting them in code.
ALTER PROCEDURE [dbo].[Insert_UserDefinedFields]
@UserDefinedFieldsId uniqueidentifier,
…
AS
SET NOCOUNT ON
DECLARE @err INT = 0
--DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
--BEGIN TRY
BEGIN TRANSACTION
INSERT INTO [BusinessMgmt].[UserDefinedFields]
([UserDefinedFieldsId],
…
OUTPUT INSERTED.RecordVersion
VALUES
(@UserDefinedFieldsId,
…
SET @err = @@ERROR
IF @err <> 0 GOTO ERRORCODE
COMMIT TRANSACTION
--END TRY
--BEGIN CATCH
-- SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY(), @err = @@ERROR
-- IF @@TRANCOUNT > 0
-- ROLLBACK TRANSACTION
-- RAISERROR(@ErrMsg, @ErrSeverity, 1)
-- RETURN @err -- does not matter what we return however. The error number is controlled through the exception not the return value
--; THROW; -- if only we could use this instead!
--END CATCH
GOTO RETURNCODE
ERRORCODE:
BEGIN
ROLLBACK TRANSACTION
END
RETURNCODE:
RETURN @err
The commented out lines show the try/catch/raiserror pattern that you have been wanting to use (which doesn’t work correctly due to what I show below). I also put a THROW in there to use instead of RAISERROR, which would work if we could use it. The "Set @err" the ‘old’ error handling pattern.
Obviously I snipped lines just to make it shorter.
If we pass null arguments to this sproc (using the ‘old’ pattern), it fails due to not allowing null inserts, as expected, resulting in SqlException with an error code of 515.
However if we switch to the ‘new’ pattern, RAISERROR won’t let 515 be the error code of the SqlException. Instead we get SqlException with an error code of 50000.
If we could use THROW instead, we do get the original SqlException with an error code of 515.
The client side exception handling mechanism we have in place and is heavily used, inspects the exception numbers. 515 is handled like this:
if (sqlExceptionNumber == 515)
throw new DatabaseNullConstraintException(exception);
With the ‘new’ pattern, since we don’t get 515 back but rather 50000, it gets handled like this:
if (sqlExceptionNumber == 50000)
throw new ApplicationDatabaseException(exception.Message, exception);
which results in a different exception type and causes code that is expecting the other type, to now fail.
In the interest of not breaking existing logic, I am afraid we are going to have to either:
a)Keep using the ‘old’ pattern, or
b)Require sql2012 or higher and use THROW with the ‘new’ pattern. RAISERROR changes behavior due to losing exception details by replacing the error number with 50000 or higher.
That was from the developer researching the current issue in our infrastructure. We need to support 2008 for 2 more years. Is there a way to get around the problem we are having with raiserror?
March 8, 2016 at 10:17 am
This is the catch block I have in a stored procedure, does this help?
begin catch;
rollback;
select
@ErrorNumber = ERROR_NUMBER(),
@ErrorLine = ERROR_LINE(),
@ErrorMessage = ERROR_MESSAGE() + '' Error occurred processing table: %s at line number: %i with original error number: %i'',
@ErrorProcedure = ERROR_PROCEDURE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
if @ErrorNumber = 1205 -- Trap deadlock
begin
WAITFOR DELAY ''00:00:00.05'' -- Wait for 50 ms
continue;
end
else begin
-- All other errors
raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState,''@BaseTable'', @ErrorLine, @ErrorNumber) with nowait;
end
end catch
NOTE: Yes, this code is in a block of dynamic SQL which is why you see two single quotes being used around string literals.
March 8, 2016 at 10:30 am
My approach is very similar to yours in structure, other than I add all the error information into a message string
[Code]
@ErrorMessage = "Error message: " + @ErrMsg + " Severity: " + CAST(@Severity AS ... etc, etc
[/Code]
and raise my trapped error with this as the error message (usually logged to an exceptions table). Depending on your preference you can add line feeds in to make it look a little easier on the eye in a debugger when you return it to the app.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply