How to creating a global temp table in one stored procedure, and use it in another stored procedure

Question

I have a dynamic stored procedure that returns a result set.

CREATE OR ALTER PROCEDURE [test].[proc] 
    (@id INT,
     @temp_table_name VARCHAR(50) = '') 
AS
BEGIN
    SET @sql = N'SELECT * FROM test.table con';

    IF (LEFT(@temp_table_name, 2) = '##')
    BEGIN
        DECLARE @move_to_temp_sql varchar(max);

        SET @move_to_temp_sql = REPLACE(@sql, 'FROM test.table con', 'INTO ' + @temp_table_name + ' FROM test.table con');
    END

    EXECUTE(@sql)
END

I'm passing the name of the temporary table when I'm calling the stored procedure from another stored procedure.

[test].[proc] 1, '##test'

I would like to access the global temp table ##test from the calling stored procedure like this:

SELECT * FROM ##test;

I get

Invalid object name '##test'

when I try this.

Please note: the structure of the table would vary so I cannot define the temp table in my calling stored procedure, it has to be done in the dynamic stored procedure.

Answer 1

Your stored procedure never created the temp table you are intended to create, rather it's just selecting the records from test.table. That's why you are failing to find the temp table.

instead of EXECUTE(@sql) you need to use EXECUTE(@move_to_temp_sql).

Also you need to declare variable @sql in your stored procedure. Please try this. You will get your desired temp table.

You can also simply

CREATE OR ALTER PROCEDURE [proc] 
    (@id INT,
     @temp_table_name VARCHAR(50) = '') 
AS
BEGIN  
    DECLARE @sql varchar(max);        

    IF (LEFT(@temp_table_name, 2) = '##')
    BEGIN

        SET @sql = 'select * INTO ' + QUOTENAME(@temp_table_name) + ' FROM test.table con';
    END

    EXECUTE(@sql)
END