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
What Does Synchronizationcontext Do
Opening a Folder in Explorer and Selecting a File
Debugging Automatic Properties
How to Convert an Object to a Byte Array in C#
Best Way to Invoke Any Cross-Threaded Code
How to Detect If a Property Exists on an Expandoobject
When Do We Need to Set Processstartinfo.Useshellexecute to True
Implicit VS Explicit Interface Implementation
Query an Xdocument for Elements by Name at Any Depth
Can't Connect to Localhost on SQL Server Express 2012/2016
Log4Net Rolling Daily Filename with Date in the File Name
Copy Constructor Versus Clone()
How to Determine the Standard Deviation (Stddev) of a Set of Values
How to Return PDF to Browser in MVC