CODEWITHSUNDEEP
sqlsql-server
tad stones
tad stones

Reputation: 57

Execute dynamic query within another query

I have a table that looks like this

sql_stmt                             table
---------                            ------
'select max(date) from table1'       table1
'select max(date) from table2'       table2
'select max(date) from table3'       table3

How can I query this table so it ends up returning the following result. Basically I want to execute the statement in the column and return the other columns as is.

max_date               table
-------                -------
2014-07-01             table1
2012-12-31             table2
2014-01-01             table3

Upvotes: 1

Views: 1425

Answers (1)

Hannah Vernon
Hannah Vernon

Reputation: 3472

The table column is unnecessary since your sql_stmt column already defines what table the query will execute against. You could do something like:

USE tempdb;

/*  first we create some test tables and data */

CREATE TABLE dbo.Statements
(
    sql_stmt NVARCHAR(255)
);

INSERT INTO dbo.Statements VALUES 
    ('select max(somedate), ''table1'' from table1')
    ,('select max(somedate), ''table2'' from table2')
    ,('select max(somedate), ''table3'' from table3');

CREATE TABLE dbo.table1
(
    SomeDate DATETIME DEFAULT (GETDATE())
)

INSERT INTO dbo.table1 VALUES (DEFAULT);
GO 100 /* insert 100 rows */

CREATE TABLE dbo.table2
(
    SomeDate DATETIME DEFAULT (GETDATE())
)

INSERT INTO dbo.table2 VALUES (DEFAULT);
GO 100 /* insert 100 rows */

CREATE TABLE dbo.table3
(
    SomeDate DATETIME DEFAULT (GETDATE())
)

INSERT INTO dbo.table3 VALUES (DEFAULT);
GO 100 /* insert 100 rows */


/* Now to actually run the sql_stmt statements  */

DECLARE @sql NVARCHAR(max);  /* MUST be a VARCHAR(MAX) for sp_executesql */


/* CASE WHEN and COALESCE are used to prevent 'UNION ALL' being placed at
    the start of the @sql string */
SELECT @sql = CASE WHEN COALESCE(@sql,'') = '' THEN '' ELSE @sql + ' UNION ALL ' END
             + sql_stmt 
FROM dbo.Statements;

SELECT @sql;  /* This allows you to see the intermediate result
                 of concatenating the sql statements */


/*  run the generated @sql statement */
EXEC sp_executesql @sql;

This returns:

enter image description here

Upvotes: 3

Related Questions