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
– Stephen Brickner, Commented Jan 19, 2016 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
– Tom Widdowson, Commented Jan 19, 2016 at 12:33
Have you seen this: stackoverflow.com/questions/14735477/… ? 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
– galenus, Commented Jan 19, 2016 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
– Tom Widdowson, Commented Jan 19, 2016 at 13:10

Community · Accepted Answer · 2017-05-23 11:45:13Z

2

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

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered Jan 19, 2016 at 13:26

Tom Widdowson

331 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user692942 Over a year ago

Why does it use ParameterDirection.Output instead of ParameterDirection.ReturnValue?

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related