How to execute Entity Framework SQLQuery stored procedure with return value parameter?
<p>I'm trying to execute and consume a return value from a stored procedure that has a return parameter for a status code. </p>

<p>Here is the stored procedure:</p>

<pre><code>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
</code></pre>

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

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

<p>Here is my EF code:</p>

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

<p>And here is <code>RunSqlStoredProcedureSingle()</code></p>

<pre><code>return unitOfWork.DataContext.Database.SqlQuery&lt;T&gt;(sql, parameters.ToArray()).FirstOrDefault();
</code></pre>

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

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

<p>I have tried with and without @ in the parameter name in the <code>SqlParameter</code> object as well. </p>

<p>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. </p>