How to Get Return Value of a 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 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?

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

INSERT return value of stored procedure

Here is your code with the issues fixed and some recommended improvements. The comments explain what and why.

SP:

CREATE PROCEDURE PERSON.NewPerson
(
  -- Use an output parameter to get values out of an SP
  @NewId INT OUT
)
AS 
BEGIN 
  -- Recommended to always list the columns you are inserting to
  -- Personally my preference is to select them (because that scales to multiple inserts), I never use the 'values' clause.
  INSERT INTO PERSON.ID_PERSON (uid)
    SELECT NEWID();

  SET @NewId = SCOPE_IDENTITY();

  -- The return statement is for a status for the SP, usually 0 for success, some other int for an error
  RETURN 0;
END
GO

Calling SP:

DECLARE @MyNewId INT;

-- Run the SP before your insert to get your new value    
EXEC PERSON.NewPerson @MyNewId OUT;

-- Then insert - ideally with a list of columns
INSERT INTO PERSON.EMPLOYEE
  SELECT @MyNewId, 1, '15434235', '10768348153', '1962-3-2', '1999-10-2', 'PETER', '', 'SMITH', 'HAMMER'

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 return the output of stored procedure into a variable in sql server

That depends on the nature of the information you want to return.

If it is a single integer value, you can use the return statement

 create proc myproc
 as 
 begin
     return 1
 end
 go
 declare @i int
 exec @i = myproc

If you have a non integer value, or a number of scalar values, you can use output parameters

create proc myproc
  @a int output,
  @b varchar(50) output
as
begin
  select @a = 1, @b='hello'
end
go
declare @i int, @j varchar(50)
exec myproc @i output, @j output

If you want to return a dataset, you can use insert exec

create proc myproc
as 
begin
     select name from sysobjects
end
go

declare @t table (name varchar(100))
insert @t (name)
exec myproc

You can even return a cursor but that's just horrid so I shan't give an example :)

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;

---

Related Topics