Getting return value from stored procedure - C#

Question

Here is my stored procedure:

ALTER proc [Core].[up_ExternalTradeInsert]
@ExecID char(16),
@SecondaryExecID char(16),
@SecurityID int,
@SecurityIDSource int,
@LastQty int,
@LastPx decimal(12, 6),
@TransactTime datetime2(3),
@Side bit, --0 Sell 1-Buy
@OrderID char(16),
@ClOrdID char(20),
@Account int,
@SenderId int
as
begin
set nocount on

insert Core.ExternalTrade(ExecID, SecondaryExecID, SecurityID, SecurityIDSource, LastQty, LastPx, TransactTime, Side, OrderID, ClOrdID, Account)
values (@ExecID, @SecondaryExecID, @SecurityID, @SecurityIDSource, @LastQty, @LastPx, @TransactTime, @Side, @OrderID, @ClOrdID, @Account)

return @@rowcount
end

And here is how I call the sp from c#:

using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["Connection"]))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("Core.up_ExternalTradeInsert", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@ExecID", execID));
                cmd.Parameters.Add(new SqlParameter("@SecondaryExecID", secondaryExecID));
                cmd.Parameters.Add(new SqlParameter("@SecurityID", securityID));
                cmd.Parameters.Add(new SqlParameter("@SecurityIDSource", securityIDSource));
                cmd.Parameters.Add(new SqlParameter("@LastQty", lastQty));
                cmd.Parameters.Add(new SqlParameter("@LastPx", lastPx));
                cmd.Parameters.Add(new SqlParameter("@TransactTime", transactTime));
                cmd.Parameters.Add(new SqlParameter("@Side", side));
                cmd.Parameters.Add(new SqlParameter("@OrderID", orderID));
                cmd.Parameters.Add(new SqlParameter("@ClOrdID", clOrdID));
                cmd.Parameters.Add(new SqlParameter("@Account", account));
                cmd.Parameters.Add(new SqlParameter("@SenderId", senderId));

                int result = cmd.ExecuteNonQuery();//.ExecuteNonQuery();
            }

The value ogf result variable is always -1, but I see that sql statement works great. When I execute the sp in MSSQl Management Studio, I see the return value as 1. Why? What am I doing wrong? I have to get the true return value from the the stored procedure.

Thanks,

Is ConfigurationManager.AppSettings["Connection"]) a good and correct connection string? — sirdank
– sirdank, Commented Aug 17, 2015 at 15:22
Have you considered using an OUTPUT parameter? The return value for an SP is intended to be a completion status. — HABO
– HABO, Commented Aug 17, 2015 at 15:23
Use int result = (int)cmd.ExecuteScalar(); and it should work. ExecuteNonQuery is not ment to return something. — Guillaume Beauvois
– Guillaume Beauvois, Commented Aug 17, 2015 at 15:24
try using an output parameter: stackoverflow.com/a/10908586/673707 — Helio
– Helio, Commented Aug 17, 2015 at 15:25

M.Ali · Accepted Answer · 2015-08-17 15:35:36Z

Expanding on what Andrei has said you should really use an OUTPUT parameter to see the number of rows affected, return value is only used to see the success/failure of the operation:

A stored procedure with an output parameter would look something like....

ALTER proc [Core].[up_ExternalTradeInsert]
@ExecID char(16),
@SecondaryExecID char(16),
@SecurityID int,
@SecurityIDSource int,
@LastQty int,
@LastPx decimal(12, 6),
@TransactTime datetime2(3),
@Side bit, --0 Sell 1-Buy
@OrderID char(16),
@ClOrdID char(20),
@Account int,
@SenderId int,
@RowCount INT OUTPUT
as
begin
set nocount on

insert Core.ExternalTrade(ExecID, SecondaryExecID, SecurityID, SecurityIDSource, LastQty, LastPx, TransactTime, Side, OrderID, ClOrdID, Account)
values (@ExecID, @SecondaryExecID, @SecurityID, @SecurityIDSource, @LastQty, @LastPx, @TransactTime, @Side, @OrderID, @ClOrdID, @Account)

  SET @RowCount =  @@rowcount;
end

And you C# code would look something like.....

using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["Connection"]))
{
    conn.Open();
    SqlCommand cmd = new SqlCommand("Core.up_ExternalTradeInsert", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@RowCount", SqlDbType.Int).Direction = ParameterDirection.Output;
    cmd.Parameters.Add(new SqlParameter("@ExecID", execID));
    cmd.Parameters.Add(new SqlParameter("@SecondaryExecID", secondaryExecID));
    cmd.Parameters.Add(new SqlParameter("@SecurityID", securityID));
    cmd.Parameters.Add(new SqlParameter("@SecurityIDSource", securityIDSource));
    cmd.Parameters.Add(new SqlParameter("@LastQty", lastQty));
    cmd.Parameters.Add(new SqlParameter("@LastPx", lastPx));
    cmd.Parameters.Add(new SqlParameter("@TransactTime", transactTime));
    cmd.Parameters.Add(new SqlParameter("@Side", side));
    cmd.Parameters.Add(new SqlParameter("@OrderID", orderID));
    cmd.Parameters.Add(new SqlParameter("@ClOrdID", clOrdID));
    cmd.Parameters.Add(new SqlParameter("@Account", account));
    cmd.Parameters.Add(new SqlParameter("@SenderId", senderId));

    cmd.ExecuteNonQuery();//.ExecuteNonQuery();
    int RowsAffected = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
}

Andrei · Accepted Answer · 2015-08-17 15:25:52Z

3

ExecuteNonQuery returns number of rows affected, but only for 3 types of operations:

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

Since stored procedure execution is neither of these, you are always getting -1. To get the result of your stored procedure, I believe you need to call ExecuteScalar.

answered Aug 17, 2015 at 15:25

Andrei

56.8k10 gold badges92 silver badges114 bronze badges

Comments

OldFart · Accepted Answer · 2015-08-17 16:46:32Z

You can add a parameter with whatever name you want to give it and a Direction of ParameterDirection.ReturnValue to get the return value from your stored procedure.

Here is a sample PROC in a database named AdHoc:

CREATE PROCEDURE [dbo].[TestProc] 
    @InputParam int
AS
BEGIN
    return @InputParam * 2
END

And here is a C# snippet that you can execute in LINQPad to test it:

using (var conn = new SqlConnection(@"Data Source=(local);Initial Catalog=AdHoc;Integrated Security=SSPI")) {
    conn.Open();

    var cmd = new SqlCommand("TestProc", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@InputParam", 21);
    cmd.Parameters.Add("@RetVal", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

    cmd.Parameters.Dump();

    cmd.ExecuteNonQuery();

    cmd.Parameters.Dump();
}

Collectives™ on Stack Overflow

Getting return value from stored procedure - C#

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related