RAISERROR in SQL Server & Catch in C#

I was reading about raising exceptions from stored procedures using RAISERROR (note the spelling) statement and catching those in c# code. To try it out, I wrote a very simple procedure that accepts a ProductID as it's sole parameter. If that ProductID exists in the table it returns the product details, else it raises an "Unknown ProductID" exception.

 

Here goes my simple little procedure:

 

USE [AdventureWorks]
GO
CREATE PROCEDURE [dbo].[uspGetProductFromProductID] 
@productID int
AS
BEGIN 
    SET NOCOUNT ON;
    SELECT * FROM Production.Product WHERE ProductID = @productID
 IF @@ROWCOUNT = 0 
  RAISERROR('Unknown ProductID: %d', 16, 1, @productID) WITH NOWAIT
END;
GO

 

The c# code was as follows:

 

     SqlDataReader reader = null;
     SqlConnection conn = new SqlConnection("Data Source=ps3119; Initial Catalog=Adventureworks; Integrated Security=SSPI");
            SqlCommand cmd = new SqlCommand("uspGetProductFromProductID", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter productID = cmd.Parameters.Add("@productID", SqlDbType.Int);
            productID.Direction = ParameterDirection.Input;
            productID.Value = 100;

            try
            {
                conn.Open();
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (SqlException sqlex)
            {
                conn.Close();
                MessageBox.Show(sqlex.Message);
            }
            catch (Exception ex)
            {
                conn.Close();
                MessageBox.Show(ex.Message);            
            }

 

ProductID 100 did not exist in the table, and hence I was expecting that the "Unknown ProductID" exception will be thrown. But it wasn't.

 

Apparently, if a resultset (empty or not) is returned from the stored procedure, the exception thrown by RAISERROR are not 'seen' in c# code.

 

Hence as a work around, I modified the SP to return no data when ProductID did not exist. This is how:

 

USE [AdventureWorks]
GO
ALTER PROCEDURE [dbo].[uspGetProductFromProductID] 
@productID int
AS
BEGIN 

    DECLARE @cnt int;
    SET NOCOUNT ON;

    SELECT @cnt = count(*) FROM Production.Product WHERE ProductID = @productID
    IF @cnt = 0
        RAISERROR('Unknown ProductID: %d', 16, 1, @productID) WITH NOWAIT

    ELSE
        SELECT * FROM Production.Product WHERE ProductID = @productID
END;
GO

 

...and now it works :)

Similar problem is faced when the UpdateCommand method of the SqlDataAdapter object uses a SQL Server stored procedure that raises an error after it returns a result set, the ADO.NET client application may not trap the error raised by SQL Server. Find out how to solve it at http://support.microsoft.com/kb/811482

 

 

More about RAISERROR and handling exceptions ...

 

1. You can hard-code the exception message as the first parameter to RAISERROR. Second parameter specifies the severity level (described later) and the third parameter indicates a state number that identifies the source from which the error was issued (if the error can be issued from more than one place).

 

RAISERROR('Unknown ProductID: %d', 16, 1, @productID)

 

 

2. To avoid hard coding message text, add your own message to the sysmessages table by using the sp_addmessage system stored procedure. You can then reference the message by using an ID. The message IDs that you define must be greater than 50,000.

 

RAISERROR( 50001, 16, 1, @ProductID )

 

 

3. Severity levels indicate the type of problem that has occured. Choose your security level according to following table.

 

Severity Level	Connection Closed?	Generates Exception?	Meaning
10 and below	No	No	Informational Messages
11-16	No	Yes	Errors that can be corrected by user
17-19	No	Yes	Resource or system errors
20-25	Yes	Yes	Fatal system errors

 

Problems :-

 

The work-around suggested is nice with one major problem—we are executing the same query twice, which can be grave in situation where the query you are firing is too expensive.

The point I am making is that you have to properly weight how important it is for you to get the exception from the SQL Server to the cost you are incurring on your server for running the query twice. Think of a table having more than 10,000 records and where you have very miniscule chance for getting ‘zero’ rows. It might be the case that the query takes seconds to run. Now to such tables we would rather prefer handling simple exceptions like “No records found” in the application rather than firing the query twice.

The work-around provided although can be used on a small table, where there is high probability of returning ‘zero’ columns (so that the query is executed just once J), but I would still argue of handling these errors in the application where it can be done very easily.

 

 

Thanks & Regards,

Arun Manglick

SMTS || Microsoft Technology Practice || Bridgestone - Tyre Link || Persistent Systems || 3023-6258

 

DISCLAIMER ========== This e-mail may contain privileged and confidential information which is the property of Persistent Systems Pvt. Ltd. It is intended only for the use of the individual or entity to which it is addressed. If you are not the intended recipient, you are not authorized to read, retain, copy, print, distribute or use this message. If you have received this communication in error, please notify the sender and delete all copies of this message. Persistent Systems Pvt. Ltd. does not accept any liability for virus infected mails.