Getting a result feedback from a stored procedure

Question

In a SQL Server 2008 I have a simple stored procedure moving a bunch of records to another table:

CREATE PROCEDURE [dbo].MyProc(@ParamRecDateTime [datetime])
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO [dbo].Table2 
SELECT 
...,
    ...
FROM [dbo].Table1 
      WHERE RecDateTime <= @ParamRecDateTime    

DELETE FROM [dbo].Table1 
      WHERE RecDateTime <= @ParamRecDateTime        
END

Running it from within SQL Server Management Studio, I get the job done and return value = 0

 DECLARE @return_value int
 EXEC    @return_value = dbo.MyProc @ParamRecDateTime = '2011-06-25 11:00:00.000'
 SELECT  'Return Value' = @return_value

But when I call the same stored procedure from an app using Entity framework, I also get the job done but the return value is "-1":

int result = myrepository.MyProc(datetimePar);
MessageBox.Show(result.ToString());

I didn't manage to find an explanation for this error, but found this discouraging post, where it's said that there is no standard for this type of return codes in SQL Server.

What is the good, reliable way of getting know of a Stored Procedure execution result when calling it from Entity Framework and when the Stored Procedure doesn't return any entities?

Answer 1

See OUTPUT attribute for SQL param of store procedure, here

Answer 2

One way to do it is to call ExecuteStoreCommand, and pass in a SqlParameter with a direction of Output:

var dtparm = new SqlParameter("@dtparm", DateTime.Now);
var retval = new SqlParameter("@retval", SqlDbType.Int);

retval.Direction = ParameterDirection.Output;

context.ExecuteStoreCommand("exec @retval = MyProc @dtparm", retval, dtparm);

int return_value = (int)retval.Value;

Originally I tried using a direction of ReturnValue:

retval.Direction = ParameterDirection.ReturnValue;

context.ExecuteStoreCommand("MyProc @dtparm", retval, dtparm);

but retval.Value would always be 0. I realized that retval was the result of executing the MyProc @dtparm statement, so I changed it to capture the return value of MyProc and return that as an output parameter.

Answer 3

For future reference: I had the same issue but needed multiple OUTPUT variables. The solution was a combination of both answers. Below is a complete sample.

public void MyStoredProc(int inputValue, out decimal outputValue1, out decimal outputValue2)
{
    var parameters = new[] { 
        new SqlParameter("@0", inputValue), 
        new SqlParameter("@1", SqlDbType.Decimal) { Direction = ParameterDirection.Output }, 
        new SqlParameter("@2", SqlDbType.Decimal) { Direction = ParameterDirection.Output } 
    };

    context.ExecuteStoreCommand("exec MyStoredProc @InParamName=@0, @OutParamName1=@1 output, @OutParamName2=@2 output", parameters);

    outputValue1 = (decimal)parameters[1].Value;
    outputValue2 = (decimal)parameters[2].Value;
}

Please note the Types used (decimal.) If another type is needed, remember to not only change it in the method argument list but also the SqlDbType.XXX.

Answer 4

using (dbContext db = new dbContext())
{
var parameters = new[]
{
new SqlParameter("@1","Input Para value"),
new SqlParameter("@2",SqlDbType.VarChar,4){ Value = "default if you want"},
new SqlParameter("@3",SqlDbType.Int){Value = 0},
new SqlParameter("@4","Input Para Value"),
new SqlParameter("@5",SqlDbType.VarChar,10 ) {   Direction = ParameterDirection.Output   },
new SqlParameter("@6",SqlDbType.VarChar,1000) {   Direction = ParameterDirection.Output   }
};
db.ExecuteStoreCommand("EXEC SP_Name @1,@2,@3,@4,@5 OUT,@6 OUT", parameters);
ArrayList ObjList = new ArrayList();
ObjList.Add(parameters[1].Value);
ObjList.Add(parameters[2].Value);
}