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.

What's stopping you? Do you get an error? I personally would create a regular temp table outside and populate it inside — siggemannen, Apr 19 '23 at 19:19
@siggemannen I get invalid object name when I try to access the temp table. — Siddharth Dinesh, Apr 19 '23 at 19:29
It's cause it goes out of scope probably. See https://stackoverflow.com/questions/3887989/global-temporary-tables-in-sql-server you need to rethink your solution. You can just pass a real table name and drop it when you're done — siggemannen, Apr 19 '23 at 19:31
Thats because the temp table does not exist when you try to create the SP. I would suggest creating a permeant staging table instead that you just truncate each time before processing. — Brad, Apr 19 '23 at 19:31
2 problems with creating a permanent/temp table at the calling procedure 1) I donot know the structure of the table before hand, i will keep varying 2) There is an issue of 2 calls updating the same table at the same time — Siddharth Dinesh, Apr 19 '23 at 19:33
Create a variable called @tablename = 'sometable_' + replace(cast(newid() as sysname), '-', '_') pass it instead of ##table. This way it will work and you get a pretty unique table name — siggemannen, Apr 19 '23 at 19:37

Kazi Mohammad Ali Nur Romel · Accepted Answer · 2023-04-20T05:07:41.543

0

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

edited Apr 20 '23 at 05:07

answered Apr 19 '23 at 19:43

Kazi Mohammad Ali Nur Romel

15,301
2
13
26

The logic of declaring and setting `@sql` inside an `IF`, then executing it afterwards escapes me. Tip: The best practice when assembling object names into dynamic SQL statements is to use [`QuoteName()`](https://docs.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql) to avoid problems with odd names, e.g. `New Table` with a space or reserved words like `From`. – HABO Apr 20 '23 at 03:21
@HABO thank you so much for the observation and advice. Best wishes. – Kazi Mohammad Ali Nur Romel Apr 20 '23 at 05:08

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

1 Answers1