CODEWITHSUNDEEP
sql-serversql-server-2012dynamic-sqltemp-tables
Piotr L
Piotr L

Reputation: 1105

SELECT INTO ##temptable from dynamic @sql

SQL Server 2012.

There is a table with sql statements, maintained by developers.

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'
     , '[email protected]'

Every now and then, an automated process kicks-in and executes one of the statements, checks if it returned any rows and if it did, emails them. Important bit is, the process adds some extras to the email depending on various conditions so it is not a simple "generate and email csv data" task (we can't simply use the built-in feature of attaching query results to the email).

The process is supposed to be as dynamic as possible. In particular, it should support any valid SQL statements that return rows.

The current implementation to retrieve and process data from the sql_statement column is something like:

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

The issue with the above solution is that it prevents developers from using WITH statements in the sql_statement column, as they cause syntax error on the EXECUTE(@actual_sql) line (for obvious reasons: you can't select from (with...)). They can use subqueries in the FROM block but I want them to be able to use any SQL code that returns rows.

Is there any workaround?

Upvotes: 0

Views: 62

Answers (1)

Adam Yan
Adam Yan

Reputation: 502

If you can manage to maintain a script that creates resultset schema for each sql_statement, this will work.

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

Upvotes: 1

Related Questions