[FIXED] How to return an id and use it directly in another stored procedure?

Issue

I want his stored procedure to return the inserted id

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

    INSERT INTO [dbo].[Address]([Address_Name])
    OUTPUT INSERTED.Address_Id
    VALUES (@Name)
END

This one the same

ALTER PROCEDURE [dbo].[InsertDocumentation_DBO]
    @Texte VARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [dbo].[Documentation]([Documentation_Text])
    OUTPUT inserted.Documentation_Id
    VALUES (@Texte)
END

And this one to use them and return her own - like using the inserted id to put it into the next stored procedure as a parameter

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 @Address_ID = [dbo].[InsertAddress_DBO] 
                       @Name = "rue de la banchiesserie 85 Golback"

    EXEC @Documentation_ID = [dbo].[InsertDocumentation_DBO] 
                             @Texte = "né en 55555 restaurant fabuleux"

    INSERT INTO [dbo].[Establishment]([Establishment_Name],[Address_Id],[Documentation_Id])
    OUTPUT inserted.Establishment_Id
    VALUES (@Establishment_Name,@Address_ID,@Documentation_ID)
END

However, I always get an error, because the stored procedure doesn't return the id when I execute it.

What is wrong in my code?

I would like to get the code I could use again and again in each stored procedure I have to execute. I already tried @@Identity, indent, scoped,... nothing works.

Solution

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.

Answered By - Kitta

Answer Checked By - Clifford M. (PHPFixing Volunteer)