0

I have ADO.Net code as below, that calls a stored procedure. Within the stored procedure, I first get the result set through a SELECT query, and then after the SELECT statement call RAISERROR if the passed @companyId parameter value does not exist. I have run unit test on this code many times with a value of @companyId so RAISEERROR gets called, but I never see the call to ExecuteReader throw an error. Any reason why this strange counter-intuitive thing is happening?

sqlCmd = new SqlCommand("dbo.xyz_sp_Attributes_GetValues", new  SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["VHA_EDM_ConnectionString"].ConnectionString));
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@attributeId", attributeId);
sqlCmd.Parameters.AddWithValue("@companyId", companyId);
sqlCmd.Parameters.AddWithValue("@attributeScope", "Company");
sqlCmd.Connection.Open();
SqlDataReader dr = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
    attributeValue = dr["AttributeValue"].ToString();
}

The stored procedure code is something like below

SELECT col1, col2, ... where CompanyId = @companyId and AttributeId = @attributeId
if @companyId not exists (select companyId from Company where CompanyId = @companyId)
begin
 set @errMessage = N'Invalid Company Error'
 RAISERROR (@errMessage, 16, 1)
end
amit_g
  • 30,880
  • 8
  • 61
  • 118
Sunil
  • 20,653
  • 28
  • 112
  • 197

1 Answers1

1

In this case there are more than one recordsets are returned. The first one is empty and that is why you don't get any error. You have to call the NextRecordset to get the error.

dr.NextResult(); // This will throw the error caused by RAISEERROR

It would be much easier to do the error checking in the client code instead of calling RAISEERROR which in any case will have to be handled as an Exception.

Catch a Sql Server RAISERROR at client side using SqlDataReader

amit_g
  • 30,880
  • 8
  • 61
  • 118
  • I am returning only one result set. Why do you say that I am returning multiple result sets? – Sunil Apr 10 '12 at 22:16
  • You are not doing it but the way SPs result is returned by ADO.NET it becomes two recordsets. Have you never run into this and then use [SET NOCOUNT ON](http://stackoverflow.com/questions/1483732/set-nocount-on-usage) – amit_g Apr 10 '12 at 22:31
  • Ok. I get you now. Thanks for your very helpful reply. – Sunil Apr 11 '12 at 22:19