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

np8igboo 于 2023-04-28 发布在其他

关注(0)|答案(1)|浏览(149)

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.

sql-server

来源：https://stackoverflow.com/questions/76058093/how-to-creating-a-global-temp-table-in-one-stored-procedure-and-use-it-in-anoth

1条答案

按热度按时间

bkhjykvo1#

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

赞(0）回复(0）举报 2023-04-28

我来回答

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

1条答案

相关问题

热门标签

最新问答