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.
