Handling Sql Custom Exception

Question

How to identify the custom error message raised from the sql stored procedure with c# code?

Stored Procedure the error will be raised like this

RAISERROR (N'This is message %s %d.', -- Message text.
       10, -- Severity,
       1, -- State,
       N'number', -- First argument.
       5); -- Second argument.

From the above, how to identify that the error is custom error message. (ie) Something like this

try{
   --actual code here
}
catch(SqlException ex)
{
    --how to check here that the exception is custom one
}

score 6 · Accepted Answer · answered Jul 19 '13 at 06:03

6

When you raise the error you can provide a MessageId instead of a Message text. This number will be found in the Number propertie of the Exception:

SQL:

    RAISERROR(50001, 12, 1)

C#:

    if (sqlException.Number == 50001)
    {
        throw new CustomSQLException(//whatever);
    }

answered Jul 19 '13 at 06:03

Romano Zumbé

7,893
4
33
55

+1, Thanks for the idea. Is is possible to add Number and message both from RAISEERROR itself? – Jul 19 '13 at 06:06
Unfortunately not. You need to decide which one you provide. Perhaps you could implement a seperate table with the message and querry this table from your Exception class? – Romano Zumbé Jul 19 '13 at 06:09
you meant sql table for this ? – Jul 19 '13 at 06:12
Yes, but it depends on importance of the message you want to provide. In a normal use case I wouldn't do this since you can provide a large range of error numbers. – Romano Zumbé Jul 19 '13 at 06:16
Just a note: `Error numbers for user-defined error messages should be greater than 50000.` – Norbert Szenasi Jan 05 '15 at 15:28
2

Another note: `Use sp_addmessage to add user-defined error messages and sp_dropmessage to delete user-defined error messages.` Both notes from: http://msdn.microsoft.com/en-us/library/ms178592.aspx – Norbert Szenasi Jan 05 '15 at 15:41

Handling Sql Custom Exception

1 Answers1