Execute a Stored Procedure in Another Stored Procedure in SQL Server

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.

Calling a stored procedure from another SQL Server Database Engine

You are missing the schema. The format is [LinkServer].[Database].[Schema].[Object]

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