Getting Return Value from Stored Procedure in C#

Calling stored procedure with return value

You need to add a ReturnValue-direction parameter to the command:

using (SqlConnection conn = new SqlConnection(getConnectionString()))
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = parameterStatement.getQuery();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("SeqName", "SeqNameValue");

// @ReturnVal could be any name
var returnParameter = cmd.Parameters.Add("@ReturnVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;

conn.Open();
cmd.ExecuteNonQuery();
var result = returnParameter.Value;
}

Setting the parameter's direction to ParameterDirection.ReturnValue instructs the SqlCommand to declare it as a variable and assign the stored procedure's return value to it (exec @ReturnValue = spMyProcedure...), exactly like you would write it in SQL.

Getting return value from stored procedure - C#

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);
}

How to get return value from stored procedure

As error suggest 'spLogin' expects parameter '@Result'

Change

SqlParameter parm = new SqlParameter("@Return", SqlDbType.Int);

to

SqlParameter parm = new SqlParameter("@Result", SqlDbType.Int);

EDIT

Also updated your procedure, return some value. Currently you are not returning anything. Also you don't need to add an extra parameter in SP.

ALTER PROCEDURE Splogin @CAD_WEB_LOGIN_USERNAME VARCHAR(60), 
@CAD_WEB_LOGIN_PASSWORD VARCHAR(60)
AS
BEGIN
Declare @MyResult as INT
SELECT @MyResult = cad_number
FROM cmacaddr
WHERE cad_web_login_username = @CAD_WEB_LOGIN_USERNAME
AND cad_web_login_password = @CAD_WEB_LOGIN_PASSWORD

RETURN @MyResult -- return value
END

Get Return Value from Stored procedure in asp.net

You need a parameter with Direction set to ParameterDirection.ReturnValue in code but no need to add an extra parameter in SP. Try this

  SqlParameter returnParameter = cmd.Parameters.Add("RetVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();

int id = (int) returnParameter.Value;

getting return value from stored procedure in vs in c#

This solved the problem:-

 ctx.Database.ExecuteSqlRaw("gross_Sal @empID, @annual_sal OUTPUT", param);
Console.WriteLine("gross sal of the emp "+param[1].Value);

I was using FromSqlRaw and I had to use EXEC to make it work like this:-

var result = ctx.Emps.FromSqlRaw($"EXEC gross_Sal  @empID, @annual_sal OUTPUT", param[0], param[1]).AsEnumerable().FirstOrDefault();
Console.WriteLine("gross salary="+param[1].Value);

But that threw an error with no message. But, using

ctx.Database.ExecuteSqlRaw("gross_Sal @empID, @annual_sal OUTPUT", param);

you can directly execute it.

I hope it helps someone who is facing the same issue.

ExecuteScalarAsync() does not return the same value returned from stored procedure

Remove this SET NOCOUNT ON in your stored procedure to get the proper value of the @@ROWCOUNT.

For more details please check this SET NOCOUNT (Transact-SQL)

Don't use RETURN -1 but rather use SELECT -1. Scalar value is a table like result with single value (1 row 1 column).

Retrieve return value from stored procedure

That output value won't get returned from ExecuteNonQuery. Try creating a reference to your return parameter like this:

var returnParameter = new SqlParameter[]
{meetingParam, areaParam, meetingLocationParam, new SqlParameter()
{
Value=0,
Direction=ParameterDirection.ReturnValue,
SqlDbType=SqlDbType.Int,
ParameterName="@MeetingId"
} }

command.Parameters.Add(returnParameter);

Then after executing,

var myReturnedValue = returnParameter.Value;

You could also change

RETURN @MeetingId;

to

SELECT @MeetingId;

and then use

var myReturnedValue = (int)command.ExecuteScalar();


Related Topics



Leave a reply



Submit