从Dynamic @sql中选择## Tumptable

Question

SQL Server 2012。

有一个带有SQL语句的表，由开发人员维护。

CREATE TABLE t
(
    id INT PRIMARY KEY CLUSTERED NOT NULL IDENTITY(1, 1)
  , sql_statement NVARCHAR(MAX) NOT NULL
  , recipient NVARCHAR(MAX) NOT NULL
);
INSERT INTO t
SELECT 'select 1 as one, 2 as two, 3 as three'
     , '[e-mail]'

时不时地，一个自动化的过程启动并执行其中一个语句，检查它是否返回任何行，如果这样做，请给他们发送电子邮件。重要的一点是，该过程根据各种条件为电子邮件添加了一些附加功能，因此这不是一个简单的"生成和电子邮件CSV数据"任务(我们不能简单地使用将查询结果附加到电子邮件的内置功能(。

该过程应该尽可能动态。特别是，它应支持任何有效的SQL语句返回行。

从SQL_Statement列检索和处理数据的当前实现是：

DECLARE @sql NVARCHAR(MAX);
SELECT @sql = sql_statement
  FROM dbo.t
 WHERE id = 1;
DECLARE @actual_sql NVARCHAR(MAX) = N'SELECT * INTO ##t from (' + @sql + N') t;';
EXECUTE(@actual_sql)
DECLARE @msg NVARCHAR(MAX);
SELECT @msg = 'plenty of heavy-lifting here, building the email message body based on the contents of ##t and various conditions'
DROP TABLE ##t
EXECUTE msdb.dbo.sp_send_dbmail
  @recipients = @recipients
  , ...                                    ...
  , @body = @msg

上述解决方案的问题是，它可以防止开发人员在sql_statement列中使用WITH语句，因为它们会在EXECUTE(@actual_sql)行上引起语法错误(出于明显的原因：您不能select from (with...)(。他们可以在FROM块中使用子查询，但我希望它们能够使用返回行的任何SQL代码。

有任何解决方法吗？

Answer 1

如果您可以维护为每个sql_statement创建Resultset架构的脚本，则将工作。

DROP TABLE IF EXISTS t1
DECLARE @pre_sql NVARCHAR(MAX) = '
        CREATE TABLE [dbo].[t1] (
            [one]   [NVARCHAR](MAX) NULL
           ,[two]   [NVARCHAR](MAX) NULL
           ,[three] [NVARCHAR](MAX) NULL
        )'
EXEC sp_executesql @pre_sql
DECLARE @sql NVARCHAR(MAX) = 'with CTE AS(select 1 as one, 2 as two, 3 as three) SELECT * from CTE'
INSERT
    INTO t1 EXEC sp_executesql @sql
SELECT * FROM t1