How to execute Entity Framework SQLQuery stored procedure with return value parameter?

Question

I'm trying to execute and consume a return value from a stored procedure that has a return parameter for a status code.

Here is the stored procedure:

DECLARE @RetVal int;
DECLARE @CustPK int;

BEGIN
    SET @RetVal = 0;
    SET NOCOUNT ON;

    SET @CustPK = (SELECT CustPK FROM tCustomer WHERE WebCustomerId = @WebCustomerId);

    -- Insert statements for procedure here
    INSERT INTO tOrder(CustPK, WebOrderId, TxCode,DelCompany, DelContact, DelAddress1, DelAddress2, DelCity, DelPostcode, DelPhone, WhiteLabel, ServiceLevelPK, DeliveryCharge, ProductionSpeed, Paid, DateOrdered, [IP Contact], vc, ProductionTIME, ProductionCharge, VDiscountExVAT, VDiscountVAT,QuoteName,[Cost Centre Reference], [Client Reference], MethodOfTransport) 
    VALUES(@CustPK, @WebOrderId, @TxCode, @DelCompany, @DelContact, @DelAddress1, @DelAddress2, @DelCity, @DelPostcode, @DelPhone, @WhiteLabel, @DeliveryType, @DeliveryCharge, @ProductionSpeed, 1, @DateOrdered, 'NEW', @Voucher, @ProductionSpeed, @ProductionCharge, @VoucherDiscount, @VoucherVAT, @PersonalReference, @CostCentreReference, @ClientReference, @MethodOfTransport)

IF @@ERROR = 0
BEGIN
    SET @RetVal = 1
END

RETURN @RetVal

This stored procedure is legacy and cannot be changed. I am trying to use Retval in my application. I am using Entity Framework's Database.SqlQuery() to execute this stored procedure.

I am creating a list of SqlParameter and then call toArray() so they can be read by EF. Normal stuff really.

Here is my EF code:

    public string PlaceOrder(IPOPPlaceOrderRequest order)
    {
        try
        {
            string storedProcedure = BuildSQLExecutionString<IPOPPlaceOrderRequest>(order, "Web_NewOrder");
            //storedProcedure = storedProcedure + ", @RetVal";
            List<SqlParameter> parameters = BuildSQLParameters<IPOPPlaceOrderRequest>(order);
            var retVal = new SqlParameter("RetVal", System.Data.SqlDbType.Int) { Direction = System.Data.ParameterDirection.Output };
            parameters.Add(retVal);
            var test = tOrderRepository.RunSqlStoredProcedureSingle<IPOPPlaceOrderRequest>(storedProcedure, parameters.ToArray());
            return "";
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

And here is RunSqlStoredProcedureSingle()

return unitOfWork.DataContext.Database.SqlQuery<T>(sql, parameters.ToArray()).FirstOrDefault();

This runs, but the value of retVal is never set to anything. I'm at a loss with this.

I have tried to do a @RetVal in the stored procedure string with and without OUT at the end.

I have tried with and without @ in the parameter name in the SqlParameter object as well.

Both the build methods just use reflection to build the string and list based on the name and values of the object passed into the method, if needed I will supply this code as well.

The stored procedure is very simple in this case. Do you really need to even use it or can you just use entity to do the same thing? Entity will return the ID when the insert command is ran, which in turn will tell you if it errored. — Stephen Brickner, Jan 19 '16 at 12:29
I've been told I need to use the stored procedure. It does some other stuff that is not relevant to the question so I did not include it. — Tom Widdowson, Jan 19 '16 at 12:33
Have you seen this: http://stackoverflow.com/questions/14735477/get-return-value-from-stored-procedure ? Difference between your version and that version is that in order to get the parameter the call to stored procedure is wrapped in a SQL fragment. — galenus, Jan 19 '16 at 12:38
I've got it working now thanks to this comment, I had seen this but going over it again gave me some fresh insight. Would you like to make it a questions and I can mark it the right answer. — Tom Widdowson, Jan 19 '16 at 13:10

score 2 · Accepted Answer · edited May 23 '17 at 11:45

I got this working by modifying the call to the stored procedure from an comment provided by @galenus

        public string PlaceOrder(IPOPPlaceOrderRequest order)
    {
        try
        {
            string storedProcedure = BuildSQLExecutionString<IPOPPlaceOrderRequest>(order, "exec @RetVal = Web_NewOrder");
            List<SqlParameter> parameters = BuildSQLParameters<IPOPPlaceOrderRequest>(order);
            var retVal = new SqlParameter("@RetVal", System.Data.SqlDbType.Int) { Direction = System.Data.ParameterDirection.Output, DbType = System.Data.DbType.Int32};
            parameters.Add(retVal);
            return tOrderRepository.RunSqlStoredProcedureSingle<object>(storedProcedure, parameters.ToArray());
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

By adding a declaration for the return parameter in the storedProcedure string it now returns the correct value.

Here's the question and answer which I got this information from: Get return value from stored procedure

Why does it use `ParameterDirection.Output` instead of `ParameterDirection.ReturnValue`? — user692942, Aug 20 '19 at 15:51

How to execute Entity Framework SQLQuery stored procedure with return value parameter?

1 Answers1