How to Use the Results of a Stored Procedure from Within Another

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 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

Execute a stored procedure from within another stored procedure's SELECT statement?

The approach what you have tried is invalid. Instead of the X as the stored procedure convert it as user-defined function. like the below

Create function dbo.fnGetTypeDetail
(
 @type varchar(50)
)
returns varchar(100)
As
Begin 
   return --do your operation;
End

And replace your query as:

SELECT name, type, dbo.fnGetTypeDetail(type) AS TypeDetail
FROM table

For sample, I created a scalar function. Based on your requirement you can create inline table valued function as per the example

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)

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 an id and use it directly in another stored procedure?

If you want to return something from stored procedure to the context of SQL query execution you may use a return statement or an output parameter. I would suggest you to use the second option. The first one is generally intended to return status of procedure execution.

ALTER PROCEDURE [dbo].[InsertAddress_DBO]
    @Name VARCHAR(50),
    @Address_ID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [dbo].[Address]([Address_Name])
    VALUES (@Name)
    SET @Address_ID = SCOPE_IDENTITY()
END

Than you can use returned value in your outer procedure

ALTER PROCEDURE [dbo].[InsertEstablishmentByStrings_DBO]
    @Establishment_Name VARCHAR(50),
    @Address_Name VARCHAR(50),
    @Documentation_Text VARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Address_ID INT ,
        @Documentation_ID INT 

    EXEC [dbo].[InsertAddress_DBO] 
        @Address_ID = @Address_ID OUTPUT,
        @Name = "rue de la banchiesserie 85 Golback"

     ...
END

An OUTPUT INSERTED clause you used doesn't returns data to the query execution context but send them to the output stream.

---

Related Topics