Tsql: Call a Stored Procedure from Another Stored Procedure and Read the Result

Calling a stored procedure from another stored procedure does not produce results

Using OUTPUT you can only get one single row.

You can use temp tables to share data between procedures. For example:

CREATE PROCEDURE sp_1 @par1 int,
                          @par2 bit,
                          ... AS
   ...
   INSERT/UPDATE/DELETE #mytemp
GO

CREATE PROCEDURE sp_2 AS
   DECLARE ...
   CREATE TABLE #mytemp (col1 int     NOT NULL,
                         col2 char(5) NULL,
                        ...)
   ...
   EXEC sp_1 @par1, @par2 ...
   SELECT * FROM #mytemp
GO

In addition, you have many other options described in an a great article written by Erland Sommarskog

UPDATE - Safety:

If temp table declared as #tempTable, then it is safe as those temp tables can only be accessed by the creating connection, and will be exist just only during of the execution of the stored procedure. However, if you create a global temp table (with two signs ##tempTable), then those tables are not safe as they are exposed to all connections of the SQL instance.

UPDATE - Availability:

As MSDN says:

A local temporary table created in a stored procedure is dropped
automatically when the stored procedure is finished. The table can be
referenced by any nested stored procedures executed by the stored
procedure that created the table. The table cannot be referenced by
the process that called the stored procedure that created the table.

So in our case, sp_2 is a outer stored procedure and sp_1 is a nested stored procedure.

How do I use the results of a stored procedure from within another?

You would declare a table variable to hold the results of the stored procedure and then loop through them in a while loop:

declare @temp table (
    idx int identity(1,1),
    field1 int,
    field2 varchar(max))

declare @result int

insert into @temp (field1, field2)
exec @result = sp_who

declare @counter int

set @counter = 1

while @counter < (select max(idx) from @temp)
begin
    -- do what you want with the rows here
    set @counter = @counter + 1
end

How to call a stored procedure that has two OUTPUT parameters from another stored procedure

Here is one way to do it:

Sample procedure with two output parameters

CREATE PROCEDURE SumAndMultiply
(
    @In int,
    @OutSum int output,
    @OutMultiply int output
)
AS

    SELECT  @OutSum = @In + @In,
            @OutMultiply = @In * @In

GO

Sample procedure that executes the first one:

CREATE PROCEDURE executeSumAndMultiply
(
    @In int
)
AS

DECLARE @Out1 int, 
        @Out2 int

EXEC SumAndMultiply @In = @In, @OutSum = @Out1 OUTPUT, @OutMultiply = @Out2 OUTPUT

SELECT @Out1 As Out1, @Out2 As Out2

GO

Execute the second procedure:

EXEC executeSumAndMultiply 3

Results:

Out1    Out2
6        9

See a live demo on rextester

How to SELECT FROM stored procedure

You can use a User-defined function or a view instead of a procedure.

A procedure can return multiple result sets, each with its own schema. It's not suitable for using in a SELECT statement.

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

SQL - Use result from a Stored Procedure in another Stored Procedure

You have a couple of different options:

1) Return an output parameter from the first stored procedure.

Create Procedure GetStockNumber
@Barcode int ,
@CodStock int OUTPUT

As

Select @CodStock = CodStock 
from TBL_Stock
Where barcode = @barcode 

to use it:

DECLARE @CodStock int

EXEC GetStockNumber @BarCode, @CodStock OUTPUT

Update TBL_Stock
Set Quantity = Quantity - @Quantity 
Where CodStock = @CodStock

2) Convert the stored procedure to a function that returns a value.

CREATE FUNCTION GetCodStock(@BarCode INT) RETURNS INT
AS
 BEGIN
   RETURN (SELECT CodStock
             FROM TBL_Stock
            Where barcode = @barcode)
 END

To use it:

Update TBL_Stock
Set Quantity = Quantity - @Quantity 
Where CodStock = dbo.GetCodStock(@BarCode)

How to use a value from one stored procedure in another?

In your stored procedure, are you either

a) Assigning the value of the count to an output parameter:

CREATE PROCEDURE GetItemCount
  @id INT,
  @count INT OUTPUT
AS
  SELECT @count = COUNT(Item) FROM tblItem WHERE ID = @id

called as:

DECLARE @count INT
EXEC GetItemCount 123, @count OUTPUT

or, b) Assigning the count value as the return value:

CREATE PROCEDURE GetItemCount
  @id INT
AS
BEGIN
  DECLARE @count INT
  SELECT @count = COUNT(Item) FROM tblItem WHERE ID = @id

  RETURN @count
END  

called as:

DECLARE @count INT
EXEC @count = GetItemCount 123

---

Related Topics