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.

CodePudding user response：

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.

CodePudding user response：

Your stored procedures should look like this, using an OUTPUT parameter, not trying to consume a RETURN value (which should never contain data) using a resultset. Also [don't] [put] [everything] [in] [square] [brackets] [unless] [you] [have] [to], [because] [all] [it] [does] [is] [hamper] [readability], and don't surround string literals with "double quotes" because that means something else in T-SQL.

CREATE OR ALTER PROCEDURE dbo.InsertAddress_DBO
    @Name       varchar(50),
    @Address_Id int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT dbo.Address(Address_Name)
      VALUES (@Name);
      
    SELECT @Address_Id = SCOPE_IDENTITY();
END
GO

CREATE OR ALTER PROCEDURE dbo.InsertDocumentation_DBO
    @Texte  varchar(50),
    @Doc_Id int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT dbo.Documentation(Documentation_Text)
      VALUES (@Texte);
      
    SELECT @Doc_Id = SCOPE_IDENTITY();
END
GO

Now, your main procedure can do this:

CREATE OR 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 
         @Name = @Address_Name,
         @Address_Id = @Address_ID OUTPUT;

    EXEC dbo.InsertDocumentation_DBO 
         @Texte = Documentation_Text,
         @Doc_Id = @Documentation_ID OUTPUT;

    INSERT dbo.Establishment
    (Establishment_Name, Address_Id, Documentation_Id)
    OUTPUT inserted.Establishment_Id, 
           inserted.Address_ID, inserted.Documentation_ID
    VALUES (@Establishment_Name,@Address_ID,@Documentation_ID);
END
GO

And you call it like this:

EXEC dbo.InsertEstablishmentByStrings_DBO
  @Establishment_Name = 'Gaston''s',
  @Address_Name       = 'rue de la banchiesserie 85 Golback',
  @Documentation_Text = 'né en 55555 restaurant fabuleux';

And get results like this:

• Fully working example on db<>fiddle