我有过滤 SQL,它返回列数不确定的查询,并希望在存储过程中使用结果。
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 上添加缺失条件
编辑2:
我无法使用下面的代码,因为 #TmpImport 在 @RecordSelectionSql 执行后被破坏。
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'.