SQL Server: Rethrow exception with the original exception number

You might be able to rethrow it like this:

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

However, you most likely lose a lost of meaning because of the %s etc placeholders in the sys.messages rows for ERROR_NUMBER()

You could do something like this to include the number and rethrow 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 chars are the original number.

But if you have nested code, then you'll end up with "00123 00456 Error text".

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

Finally, you could pass it out the return value.

I asked a question on this: SQL Server error handling: exceptions and the database-client contract

If you use BEGIN TRY/BEGIN CATCH in T-SQL you loose the original engine raised exception. You are not supposed to manually raise system errors, so you can't re-raise the original error number 2627. The T-SQL error handling is not similar to C#/C++ error handling, there are no means to re-throw the original exception. There are a number of reasons why this limitation exists, but suffice to say that is in place and you can't ignore it.

However there are no limitations to raising your own error codes, as long as they are above the 50000 range. You register 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 would raise the new error:

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

In the C# code you would look for the error number 50001 instead of 2627:

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

I whish there was a simpler answer, but unfortunately this is the way things are. The T-SQL exception handling does not integrate seamlesly into the CLR exception handling.