0

In SQL Server Management Studio:

  1. Right click the procedure
  2. Execute
  3. Do not enter value for the OUTPUT parameter
  4. Enter value for another string parameter
  5. The correct value is returned (ex: 12+)

Calling code:

DECLARE @return_value int,
        @CustomerID bigint

EXEC @return_value = [dbo].[InsertCustomer]
            @CustomerID = @CustomerID OUTPUT,
            @Name = N'CustomerName'

SELECT @CustomerID as N'@CustomerID'
SELECT 'Return Value' = @return_value

In application code:

  1. Run the following code
  2. The returned value for CustomerID is always 1

Code:

ObjectParameter ob = new ObjectParameter("CustomerID", typeof(long));
var CustomerID = db.InsertCustomer(ob, "CustomerName");

I attempted to change the new ObjectParameter(,) second parameter by passing a type and often object by value (ex: 0, 1 etc) but with no avail.

What could have went wrong here?

Update:

This is how the Entity Framework procedure code look like:

public virtual int InsertCustomer(ObjectParameter customerID, string name)
{
    var nameParameter = name != null ?
            new ObjectParameter("Name", name) :
            new ObjectParameter("Name", typeof(string));

    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("InsertCustomer", customerID, nameParameter);
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
usefulBee
  • 9,250
  • 10
  • 51
  • 89

1 Answers1

2

Straight ADO:

Please refer to this link.. You need to specify your output parameter in the proc and you need to specify it in the calling code.

SqlParameter outParam = new SqlParameter("CustomerID", 0);

using (SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["StringName"].ConnectionString))
{
    using (SqlCommand cmd = new SqlCommand("InsertCustomer", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;              
        cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = CustomerName;
                    cmd.Parameters.Add(outParam.ParameterName, SqlDbType.BigInt).Direction = ParameterDirection.Output;                 
        con.Open();

        var CustomerID = Convert.ToInt64(cmd.Parameters["CustomerID"].Value);
    }
}

Entity Framework:

Refer to this link 

ObjectParameter ob = new ObjectParameter("CustomerID", 0);

// Wrong
var CustomerID = db.InsertCustomer(ob, "CustomerName");

// Right
db.InsertCustomer(ob, "CustomerName");
var CustomerID = (long)ob.Value;
Community
  • 1
  • 1
Chris Bartlett
  • 312
  • 1
  • 11
  • Could you please explain what you mean by "specify your output parameter in...the calling code"? The parameter is already specified in the sql procedure, not sure about the calling code. I updated the question with the EF code. – usefulBee Feb 07 '17 at 19:20
  • 1
    For starters, the above was meant for straight ADO, which I personally find a bit easier to use stored procedures for, but that is a matter of opinion. Here is a link that should help you out: http://stackoverflow.com/questions/22068027/executing-sql-stored-procedure-with-output-parameter-from-entity-framework – Chris Bartlett Feb 07 '17 at 19:27
  • If EF is selected as the main technology, I don't see a reason to using straight ADO since it is more verbose and requires more lines of code. A few times I had to move away from EF to ADO due to limitations. However, your link was very helpful, specifically the last answer by @Augis http://stackoverflow.com/a/41488387/2093880 – usefulBee Feb 07 '17 at 19:58
  • 1
    I can see where you are coming from on the want to not use ADO with EF, but from personal experience with ORMs, it is sometimes easier to just write straight ado for stored procedures with a mutual mapping function to drop your results set into a C# object. I have not found a good ORM solution for stored procedures that I have liked. More lines of code does not necessarily equate to bad. – Chris Bartlett Feb 07 '17 at 20:01