How to get stored procedure @@ERROR value in entity framework ?

Question

Hello,

I'm using .NET 3.5 SP1.

I have a stored procedure returning a result of a transaction as shown below:

---

Create PROCEDURE SetPrice
     @itemId int,
     @price int
AS
DECLARE @myERROR int -- Local @@ERROR
       , @myRowCount int -- Local @@ROWCOUNT
SET NOCOUNT ON
 BEGIN TRAN1 

 UPDATE item_price_table SET price=@price   WHERE itemid=@itemId 
 UPDATE order_table SET price=@price   WHERE itemid=@itemId 
 SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT
 IF @myERROR != 0 GOTO HANDLE_ERROR  
 COMMIT TRAN1 -- No Errors, so go ahead
 RETURN 0
HANDLE_ERROR:
    ROLLBACK TRAN1
    RETURN @myERROR

---

I’m trying to get @@ERROR status code returned. In EF v1, I created FunctionImport 'SetPrice' and called it by creating method in 'mycontext' class as shown below:

---

public int SetPrice(int itemId, int price) {       
 Int32 result = -1;
 EntityCommand cmd = ((EntityConnection)this.Connection).CreateCommand();
 cmd.CommandType = System.Data.CommandType.StoredProcedure;
 cmd.CommandText = "myContext.SetPrice";
 cmd.Parameters.AddWithValue("itemId", itemId);
 cmd.Parameters.AddWithValue("price", price);
 EntityParameter retParameter = new EntityParameter(); 
 retParameter.Direction = System.Data.ParameterDirection.ReturnValue;
 cmd.Parameters.Add(retParameter);
 cmd.Connection.Open();
 cmd.ExecuteNonQuery();
 result = Convert.ToInt32(retParameter.Value);
 cmd.Connection.Close();
 return result;
}

---

This resulted in following error:

The data reader returned by the store data provider does not have enough columns for the query requested. at System.Data.EntityClient.EntityCommandDefinition.ConstantColumnMapGenerator.System.Data.EntityClient.EntityCommandDefinition.IColumnMapGenerator.CreateColumnMap(DbDataReader reader) at System.Data.EntityClient.EntityCommandDefinition.Execute(EntityCommand entityCommand, CommandBehavior behavior) at System.Data.EntityClient.EntityCommand.ExecuteReader(CommandBehavior behavior) at System.Data.EntityClient.EntityCommand.ExecuteScalar[T_Result](Func`2 resultSelector) at System.Data.EntityClient.EntityCommand.ExecuteScalar()

Please tell how to resolve the issue.

Thank You.

Answer 1

EF v1 has a problem returning scalar values from a stored proc. This thread has more information.

Answer 2

A long answer, but for SQL 2005+ you don't need to use @@ERROR. For SQL Server 2000, you need to use if differently... Because you are using a stored proc, it's irrelevent if you're using Entity Framework on Windows or jTDS on Solaris...

(As an aside, the SQL is flawed anyway)

This may not trap the error if the first update fails. @@ERROR has the value for the last statement only:

 UPDATE item_price_table SET price=@price   WHERE itemid=@itemId 
 UPDATE order_table SET price=@price   WHERE itemid=@itemId 
 SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT

You'll need to check after every DDL statement:

 UPDATE item_price_table SET price=@price   WHERE itemid=@itemId 
 SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT
 IF @myERROR <> 0 GOTO HANDLE_ERROR

 UPDATE order_table SET price=@price   WHERE itemid=@itemId 
 SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT
 IF @myERROR <> 0 GOTO HANDLE_ERROR

However, this is still not useful. You can then rethrow the using RAISERROR and the client simply needs to know an exception happened:

 ...
 HANDLE_ERROR:
     ROLLBACK TRAN1
     RAISERROR ('Oops: %i', 16, 1, @myERROR) --to pass error number if you really want

Or simply use TRY/CATCH in SQL Server 2005+. And RAISERROR in the catch block

Regardless, in either case you don't need to read @@ERROR or return value... you rely on the SQLException being caught. For me, a stored proc returns either meaningful data or an exception (and any data is ignored or invalid)

PS: why store @@ROWCOUNT when it's not used?

Answer 3

Thanks. As mentioned in that post, a SELECT statement is required. When added a SELECT before RETURN, the query executed properly.