Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
301 views
in Technique[技术] by (71.8m points)

c# - SQL Server: Rethrow exception with the original exception number

I am using a TRY CATCH block in a stored procedure where I have two INSERT instructions.

If something goes wrong, the CATCH block takes care of rolling back all changes made and it works fine, except one thing!

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

In the Message property of the exception I can see the original exception number and message formated.

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();
    }
}

Right now the return value is already being used. I guess that I could use an output parameter to get the exception number, but is that a good idea?

What can I do to get the exception number? Thanks

PS: This is needed because I have two INSERT instructions.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...