Get Return Value from Stored Procedure

Get RETURN value from stored procedure in SQL

This should work for you. Infact the one which you are thinking will also work:-

 .......
 DECLARE @returnvalue INT

 EXEC @returnvalue = SP_One
 .....

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

Getting the return value from stored procedure and using it in program

The SQL is more complicated than it needs to be - you could use

CREATE Procedure Check_Previous_Passwords
    @ua_pk uniqueidentifier,
    @IncomingPassword varchar(25)
AS
    SELECT COUNT(*)
    FROM User_Passwords up 
    WHERE up.ua_fk = @ua_pk
        AND @IncomingPassword = up.up_Password

and the VB which uses it needs to be a function so that it can return a value:

Option Strict On
' ...'
Public Function Check_Previous_Passwords(ByVal User As FoundationLibrary.User) As Boolean
    Dim isSamePassword As Integer
    Dim objCommand As New SqlCommand("Check_Previous_Passwords", DatabaseInterface_.Connection)
    objCommand.CommandType = CommandType.StoredProcedure
    objCommand.Parameters.Add(New SqlParameter With {.ParameterName"@ua_pk", .SqlDbType = SqlDbType.UniqueIdentifier, .Value = ua_pk_})
    objCommand.Parameters.Add(New SqlParameter With {.ParameterName = "@IncomingPassword", .SqlDbType = SqlDbType.VarChar, .Size = 25, .Value = ua_Password_})
    DatabaseInterface_.Open()
    isSamePassword = CInt(objCommand.ExecuteScalar)
    DatabaseInterface_.Close()

    User.ua_ResetPassword_ = (isSamePassword = 1)

    Return User.ua_ResetPassword_

End Function

I changed the AddWithValue parts to a version which works reliably. You should avoid AddWithValue - it will only bring you misery eventually: Can we stop using AddWithValue() already?

How to get return value from stored procedure with dynamic SQL query

You don't really need a cursor for this anyway, you could generate the SQL and execute it in one shot dynamically.

But in any case, it's far better to just query the system views for rowcounts

SELECT
  SchemaName = SCHEMA_NAME(t.schema_id),
  TableName = t.name,
  TotalRowCount = (
      SELECT SUM(p.rows)
      FROM sys.partitions p
      WHERE t.object_id = p.object_id
        AND p.index_id IN ( 0, 1 )  -- heap or clustered
  ),
  TotalColumns = (
      SELECT COUNT(*)
      FROM sys.columns c
      WHERE t.object_id = c.object_id
  )
FROM sys.tables t;

Get return value from stored procedure

I found it!
I can read the return value with an output parameter that has to be used in this way:

// define a new output parameter
var returnCode = new SqlParameter();
returnCode.ParameterName = "@ReturnCode";
returnCode.SqlDbType = SqlDbType.Int;
returnCode.Direction = ParameterDirection.Output;

// assign the return code to the new output parameter and pass it to the sp
var data = _context.Database.SqlQuery<Item>("exec @ReturnCode = spItemData @Code, @StatusLog OUT", returnCode, code, outParam);

Cannot get return value from stored procedure

First you decide that you want to get output parameter or return value parameter.

1. to get return value parameter. You don't have to pass any extra parameter to stored procedure. just add below lines

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

2. to get output parameter. All the steps you done are fine. just replace

   returnParm.Direction = ParameterDirection.ReturnValue;
   

with

  returnParm.Direction = ParameterDirection.Output;

Return value from one stored procedure to another

Add

@Status int OUTPUT

in the pro_ForeignKeyCheck so it starts with

CREATE PROCEDURE dbo.pro_ForeignKeyCheck1
@tableName VARCHAR(100),
@columnName VARCHAR(100),
@idValue int,
@Status int OUTPUT

and at the end of it did as follow

--select coalesce(@fkFound,0)
select @Status = coalesce(@fkFound,0)
--return 0

stop the last to line and add new one

In the other stored procedure, call it as follows

EXEC  pro_ForeignKeyCheck1 'tb_M_admin','admin_id', 0 ,@exit output 
select @exit

and now the return value will be used.

Thanks to all

How to retrieve stored procedure return value with Dapper

To enable use of the RETURN statement in SQL the C# becomes...

var _params = new DynamicParameters{ @ProductID = prodId, @Name = name };
_params.Add(
    name: "@RetVal",
    dbType: DbType.Int32,
    direction: ParameterDirection.ReturnValue
    );
var returnCode = cn.Execute(
    sql: "AddProduct",
    param: _params,
    commandType: CommandType.StoredProcedure);
return _params.Get<int>("@RetVal");

It's not the implementation I was hoping for, but it works.

---

Related Topics