Search code examples
c#sql-server-2008raiserror

Handling Sql Custom Exception

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
}
Solution

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