Execute dynamic query within another query

Question

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

You need to use dynamic SQL to execute SQL statements in a string. That involves sp_executesql (if you do it right). — Gordon Linoff
– Gordon Linoff, Commented Aug 1, 2014 at 16:05

Hannah Vernon · Accepted Answer · 2014-08-01 16:56:56Z

3

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

edited Aug 1, 2014 at 16:56

answered Aug 1, 2014 at 16:42

Hannah Vernon

3,4711 gold badge34 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SQLChao Over a year ago

clever use of case and coalesce. I basically did the same thing using for xml path but I manually chopped off the final union all. Your solution is much cleaner.

Collectives™ on Stack Overflow

Execute dynamic query within another query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related