Select from stored procedure that uses temp tables

Don't do this. But if you HAVE to do this...

USE YOUR_DATABASE

GO

CREATE OR ALTER PROCEDURE DBO.EXAMPLE_SELECT_FROM_PROCEDURE_WITH_TEMP_TABLES
AS

BEGIN
    /* 
    This procedure creates a simple temp table of numbers and selects from it.
    This is used in an example showing how to select from a procedure that 
    utilizes temp tables (which is normally not possible)
    */

    SET NOCOUNT ON;

    BEGIN
        DROP TABLE IF EXISTS #TMP;
        /*Create table first, not select into, otherwise you will lock the catalog */
        CREATE TABLE #TMP (RESULT_COLUMN1 INT);
    END;
    BEGIN
        WITH CTE
        AS
        (SELECT
                1 AS NUM
            UNION ALL
            SELECT
                NUM + 1
            FROM CTE
            WHERE NUM <= 100)

        INSERT INTO #TMP
        SELECT
            NUM AS RESULT_COLUMN1
        FROM CTE;
    END;

    SELECT
        RESULT_COLUMN1
    FROM #TMP;

END;



/* Finally, using a previously established loopback linked server, we 'query' the procedure.
WITH RESULT SETS is *required* to make this work!
Details on creating a loopback linked server: https://blog.sqlauthority.com/2017/06/08/sql-server-quickest-way-add-loopback-linked-server-openquery/
*/

--this could be created as a view!
SELECT
    *
FROM OPENQUERY(YOUR_DB_LOOPBACK, 'exec YOUR_DATABASE.dbo.EXAMPLE_SELECT_FROM_PROCEDURE_WITH_TEMP_TABLES WITH RESULT SETS ((RESULT_COLUMN1 INT))')