Select from Stored Procedure

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.

How to select from stored procedure output

You could store the result of the stored procedure into a temporary table (or a table variable, or even a physical table) and then query that one:

--drop table if exists #temp
create table #temp(name nvarchar(100), rows int, reserved nvarchar(100), data nvarchar(100), index_size nvarchar(100), unused nvarchar(100))

insert into #temp
exec sp_spaceused 'dummytable'

select [rows], [reserved] 
from #temp

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.

Stored procedure in SQL Server with select query then if condition and then insert query

Here's how I would write it:

CREATE PROCEDURE sp_CreateExpiryDocumentFollowup
(
    --@param1 int = null -- this is no longer needed
    --@param2 int = null -- this is no longer needed
    @param3 int = 1,
    @param4 bit = 0
    --,@followupid int = null -- -- this is no longer needed
)
AS BEGIN

    INSERT INTO [TABLE_B] (VALUE_1, VALUE_2, VALUE_3, VALUE_4)
    SELECT [TABLE_A].[VEHICLE_ID], [TABLE_A].[VEHICLE_DOCUMENT_ID], @param3, @param4
    FROM [TABLE_A]
    LEFT JOIN [TABLE_B] 
        -- as stated in the comments to the questions, 
        -- Shouldn't this condition be `=` instead of `!=`?
        ON [TABLE_B].[VEHICLE_DOCUMENT_ID] != [TABLE_A].[VEHICLE_DOCUMENT_ID]
    WHERE [TABLE_A].[STATUS] = 1
    AND [TABLE_B].[FOLLOWUP_ID] IS NULL

END
GO

Instead of first selecting and then inserting, you can do an insert...select operation which leads to a shorter, more readable code.
This way, if the select statement doesn't return any rows, nothing gets inserted into the target table.

Also, you can't use equality operators on NULL in SQL. Instead, you can only use IS NULL or IS NOT NULL (Or NOT IS NULL if you like that better).

Please note that if the select statement returns more than one row, all of them will be inserted into the target table.

---

Related Topics