Getting A Result Feedback From A Stored Procedure In Entity Framework

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 O

Solution 1:

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.

Solution 2:

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

Solution 3:

See OUTPUT attribute for SQL param of store procedure, here

Solution 4:

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.

publicvoidMyStoredProc(int inputValue, outdecimal outputValue1, outdecimal 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.