如何从生成的sql返回本地临时表 [英] How to return local temporary table from generated sql

问题描述

我有过滤 SQL，它返回列数不确定的查询，并想在存储过程中使用结果.

I have filtering SQL that returns query with uncertain number of columns, and want to use results in stored procedure.

DECLARE @RecordSelectionSql VARCHAR(MAX)
SET @RecordSelectionSql = (SELECT SQLQUERY FROM RecordSelection WHERE Id = @Id) + ' AND ([Active] = 1)'

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += ',' + CHAR(13) + CHAR(10) + CHAR(9) + name + ' ' + system_type_name
FROM sys.dm_exec_describe_first_result_set(@RecordSelectionSql, NULL, 0);

SELECT @sql = N'CREATE TABLE #TmpImport
(' + STUFF(@sql, 1, 1, N'') + '
);';
EXEC (@sql)

INSERT INTO #TmpImport
EXEC (@RecordSelectionSql)

但是我收到错误

Invalid object name '#TmpImport'.

如何正确编码这部分?

在 RecordSelection 上添加了缺失条件

added missing condition on RecordSelection

我无法使用下面的代码，因为在执行 @RecordSelectionSql 后 #TmpImport 被破坏了.

I cannot use code below because #TmpImport destroyed after @RecordSelectionSql being executed.

DECLARE @RecordSelectionSql AS VARCHAR(MAX)
SET @RecordSelectionSql = 'SELECT X.* INTO #TmpImport FROM (' 
    + (SELECT SQLQUERY FROM RecordSelection WHERE Id = @Id) + ' AND ([Active] = 1) AS X'
EXEC (@RecordSelectionSql)
SELECT * FROM #TmpImport

给出同样的错误

Invalid object name '#TmpImport'.

推荐答案

临时表仅在创建它们的会话中可用.对于动态 SQL，这意味着它在动态 SQL 运行后不可用.您可以选择:

Temporary tables are only available within the session that created them. With Dynamic SQL this means it is not available after the Dynamic SQL has run. Your options here are to:

1. 创建一个全局临时表，它将在您的会话之外持续存在，直到它以另一种方式从 TempDB 中显式删除或清除，使用双哈希:create table ##GlobalTemp
   --要在下面合并 Radu 非常相关的评论:因为此表在您的会话之外仍然存在，所以您需要确保您没有创建其中的两个或有两个不同的进程试图处理其中的数据.您需要有一种方法来唯一标识要处理的全局临时表.
2. 您可以创建一个普通表，然后记得再次删除它.
3. 在动态 SQL 脚本中包含需要引用临时表的任何逻辑

不过，对于您的特定实例，您最好只执行一个 select into，它会根据所选数据生成您的表结构.

For your particular instance though, you are best off simply executing a select into which will generate your table structure from the data that is selected.

这篇关于如何从生成的sql返回本地临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！