英文:
How to creating a global temp table in one stored procedure, and use it in another stored procedure
问题
I have a dynamic stored procedure that returns a result set.
创建或更改存储过程[test].[proc]
(@id INT,
@temp_table_name VARCHAR(50) = '')
AS
BEGIN
SET @sql = N'从测试表 con 中选择 *';
IF (LEFT(@temp_table_name, 2) = '##')
BEGIN
DECLARE @move_to_temp_sql varchar(max);
SET @move_to_temp_sql = REPLACE(@sql, '从测试表 con', 'INTO ' + @temp_table_name + ' FROM 测试表 con');
END
执行(@sql)
END
当我从另一个存储过程调用存储过程时，我传递了临时表的名称。
[test].[proc] 1, '##test'
我想这样访问全局临时表##test，如下所示：
SELECT * FROM ##test;
当我尝试这样做时，我收到错误消息
无效的对象名称'##test'
请注意：表的结构会有所变化，因此我不能在我的调用存储过程中定义临时表，必须在动态存储过程中进行定义。
英文:
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.
答案1
得分: 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
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论