SQL Server: Rtrow exception with source exception number

Question

I use the TRY CATCH block in a stored procedure where I have two INSERT statements.

If something goes wrong, the CATCH block will take care to undo all the changes made, and it will work fine, except for one!

The exception caught by my ASP.NET application is the SqlException with the number 50000. This is not the original number! (the number I was expecting was 2627)

In the Message property of an exception, I see the original exception number and the generated message.

How can I get the original exception number?

try { // ... code } catch (SqlException sqlException) { switch (sqlException.Number) { // Name already exists case 2627: throw new ItemTypeNameAlreadyExistsException(); // Some other error // As the exception number is 50000 it always ends here!!!!!! default: throw new ItemTypeException(); } } 

The return value is currently in use. I suppose I can use the output parameter to get the exception number, but is this a good idea?

What can I do to get an exception number? Thanks

PS: This is necessary because I have two INSERT statements.

Answer 1

Thank you guys for your answers. Getting the error from the re-throw message was what I already did.

@gbn I also liked the answer of gbn, but I will stick to this answer as it works best and I publish it here, hoping that it will also be useful to others.

The answer is to use transactions in the application. If I do not catch the exception in the stored procedure, I will get the original number in the SqlException object. After detecting the source exception in the application, I write the following code

 transaction.Rollback(); 

Otherwise:

 transaction.Commit(); 

It is much easier than I expected before!

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx

Answer 2

You may be able to change it as follows:

 .. END TRY BEGIN CATCH DECLARE @errnum int; SELECT @errnum = ERROR_NUMBER(); RAISERROR (@errnum, 16, 1); END CATCH 

However, most likely you will lose meaning due to% s placeholders, etc. in sys.messages lines for ERROR_NUMBER ()

You can do something like this to include a number and change the original message

 .. END TRY BEGIN CATCH DECLARE @errnum nchar(5), @errmsg nvarchar(2048); SELECT @errnum = RIGHT('00000' + ERROR_NUMBER(), 5), @errmsg = @errnum + ' ' + ERROR_MESSAGE(); RAISERROR (@errmsg, 16, 1); END CATCH 

The first 5 characters are the original number.

But if you have nested code, then you will get the message "00123 00456 Error text".

Personally, I only deal with SQL Exception numbers to separate my errors (50,000) from Engine errors (for example, missing parameters), where my code does not run.

Finally, you can pass this return value.

I asked a question about this: SQL Server Error Handling: Exceptions and Database Client Contract

Answer 3

If you use BEGIN TRY / BEGIN CATCH in T-SQL, you lose the exception from the original engine. You do not have to manually raise system errors, so you cannot re-raise the original error number 2627. T-SQL error handling is not like C # / C ++ error handling, there is no means to throw the original exception again. There are a number of reasons why this restriction exists, but suffice it to say that it is in place, and you cannot ignore it.

However, there are no restrictions on collecting your own error codes if they are above the 50000 range. You log your own messages using sp_addmessage when the application is installed:

 exec sp_addmessage 50001, 16, N'A primary key constraint failed: %s'; 

and in your T-SQL you will raise a new error:

 @error_message = ERROR_MESSAGE(); raiserror(50001, 16, 1, @error_message; 

In C # code, you should look for error number 50001 instead of 2627:

 foreach(SqlError error in sqlException.Errors) { switch (error.Number) { case 50001: // handle PK violation case 50002: // } } 

I heard that there was a simpler answer, but unfortunately it is. T-SQL exception handling does not integrate selylesly into CLR exception handling.

Answer 4

Here is the code I use to solve this problem (called from CATCH). It inserts the original error number into the message body:

 CREATE PROCEDURE [dbo].[ErrorRaise] AS BEGIN DECLARE @ErrorMessage NVARCHAR(4000) DECLARE @ErrorSeverity INT SET @ErrorMessage = CONVERT(VARCHAR(10), ERROR_NUMBER()) + ':' + ERROR_MESSAGE() SET @ErrorSeverity = ERROR_SEVERITY() RAISERROR (@ErrorMessage, @ErrorSeverity, 1) END 

Then you can check for example SqlException.Message.Contains("2627:") .

Answer 5

I thought about this topic for a while and came up with a very simple solution that I had not seen before, so I wanted to share this:

Since it is impossible to repeat the same error, you need to throw an error that is very easy to correlate with the original error, for example, adding a fixed number, for example 100000, to each system error.

After the new mapped messages are added to the database, any system error with a fixed offset of 100000 can be executed.

Here is the code for creating mapped messages (this needs to be done only once for the entire instance of SQL Server. Avoid collisions with other user messages by adding the appropriate offset, for example, 100000):

  DECLARE messageCursor CURSOR READ_ONLY FOR select message_id + 100000 as message_id, language_id, severity, is_event_logged, [text] from sys.messages where language_id = 1033 and message_id < 50000 and severity > 0 DECLARE @id int, @severity int, @lang int, @msgText nvarchar(1000), @withLog bit, @withLogString nvarchar(100) OPEN messageCursor FETCH NEXT FROM messageCursor INTO @id, @lang, @severity, @withLog, @msgText WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN set @withLogString = case @withLog when 0 then 'false' else 'true' end exec sp_addmessage @id, @severity, @msgText, 'us_english', @withLogString, 'replace' END FETCH NEXT FROM messageCursor INTO @id, @lang, @severity, @withLog, @msgText END CLOSE messageCursor DEALLOCATE messageCursor 

And this is the code for creating the newly created error codes that have the offset correction from the source code:

  SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE() set @MappedNumber = @ErrorNumber + 100000; RAISERROR ( @MappedNumber, @ErrorSeverity, 1 ); 

There is one small caveat: in this case, you cannot provide the message yourself. But you can work around this by adding extra% s to the sp_addmessage call, or changing all the matching messages to your own template and providing the correct parameters in the raiseerror call. It is best to set all messages to the same template, for example, "% s (line:% d procedure:% s)% s", so you can provide the original message as the first parameter and add the real procedure and line and your own message like other parameters.

In the client, now you can perform all the usual exception handling, as the original message would be selected, you only need to remember about adding a correction offset. You can even handle original and repeated exceptions with the same code as this:

 switch(errorNumber) { case 8134: case 108134: { } } 

Thus, you don’t even need to know if this is a repeated or original error, it is always correct, even if you forgot to process your errors and missed the original error.

There are some improvements mentioned in other sections regarding raising messages that you cannot raise or declare that you cannot use. Those that stayed here to show only the essence of the idea.

Answer 6

Since SQL Server <2010 does not have the ability to re-throw, the correct way to do this is to use a transaction and explicitly check the error state (think more of “C” than “C ++ / C #”).

eg. the SP body would look something like this:

 CREATE PROCEDURE [MyProcedure] @argument1 int, @argument2 int, @argument3 int AS BEGIN DECLARE @return_code int; IF (@argument1 < 0) BEGIN RAISERROR ("@argument1 invalid", 16, 1); END; /* Do extra checks here... */ /* Now do what we came to do. */ IF (@@ERROR = 0) BEGIN BEGIN TRANSACTION; INSERT INTO [Table1](column1, column2) VALUES (@argument1, @argument2); IF (@@ERROR = 0) BEGIN INSERT INTO [Table2](column1, column2) VALUES (@argument1, @argument3); END; IF (@@ERROR = 0) BEGIN COMMIT TRANSACTION; SET @return_code = 0; END ELSE BEGIN ROLLBACK TRANSACTION; SET @return_code = -1; /* Or something more meaningful... */ END; END ELSE BEGIN SET @return_code = -1; END; RETURN @return_code; END; 

This is a solution that will work in a hosted environment (where you probably won't be able to create your own error messages).

Although not as convenient as using exceptions, this approach will save system error codes. It also has the advantage (dis) of being able to return multiple errors for execution.

If you just want to crack the first error, either insert return statements, or if you feel brave, GOTO is an error block (remember: Going to the statement is considered malicious ), for example:

(Elements from this are taken from ASP.NET account management)

 CREATE PROCEDURE [MyProcedure] @argument1 int, @argument2 int, @argument3 int AS BEGIN DECLARE @return_code int = 0; DECLARE @tranaction_started bit = 0; /* Did we start a transaction? */ IF (@argument1 < 0) BEGIN RAISERROR ("@argument1 invalid", 16, 1); RETURN -1; /* Or something more specific... */ /* Alternatively one could: SET @return_code = -1; GOTO ErrorCleanup; */ END; /* Do extra checks here... */ /* Now do what we came to do. */ /* If no transaction exists, start one. * This approach makes it safe to nest this SP inside a * transaction, eg in another SP. */ IF (@@TRANCOUNT = 0) BEGIN BEGIN TRANSACTION; SET @transaction_started = 1; END; INSERT INTO [Table1](column1, column2) VALUES (@argument1, @argument2); IF (@@ERROR <> 0) BEGIN SET @return_code = -1; /* Or something more specific... */ GOTO ErrorCleanup; END; INSERT INTO [Table2](column1, column2) VALUES (@argument1, @argument3); IF (@@ERROR <> 0) BEGIN SET @return_code = -1; /* Or something more specific... */ GOTO ErrorCleanup; END; IF (@transaction_started = 1) BEGIN /* ONLY commit the transaction if we started it! */ SET @transaction_started = 0; COMMIT TRANSACTION; END; RETURN @return_code; ErrorCleanup: IF (@transaction_started = 1) BEGIN /* We started the transaction, so roll it back */ ROLLBACK TRANSACTION; END; RETURN @return_code; END; 

Answer 7

I am using the following pattern:

 CreatePROCEDURE [dbo].[MyProcedureName] @SampleParameter Integer, [Other Paramaeters here] As Set NoCount On Declare @Err Integer Set @Err = 0 Declare @ErrMsg VarChar(300) -- ---- Input parameter value validation ------ Set @ErrMsg = ' @SampleParameter ' + 'must be either 1 or 2.' If @SampleParameter Not In (1, 2) Goto Errhandler -- ------------------------------------------ Begin Transaction Set @ErrMsg = 'Failed to insert new record into TableName' Insert TableName([ColumnList]) Values [ValueList]) Set @Err = @@Error If @Err <> 0 Goto Errhandler -- ------------------------------------------ Set @ErrMsg = 'Failed to insert new record into Table2Name' Insert TableName2([ColumnList]) Values [ValueList]) Set @Err = @@Error If @Err <> 0 Goto Errhandler -- etc. etc.. Commit Transaction Return 0 /* *************************************************/ /* ******* Exception Handler ***********************/ /* *************************************************/ /* *************************************************/ ErrHandler: If @@TranCount > 0 RollBack Transaction -- ------------------------------------ RaisError(@ErrMsg, 16, 1 ) If @Err = 0 Set @Err = -1 Return @Err