How to EXEC a query generated in a CTE
common-table-expression, sql, sql-server
Solution
You can not use `exec` as a part of a `CTE`.
From WITH common_table_expression (Transact-SQL)
A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns.
What you can do instead is to store the result from your query in a temp table, loop over the rows and execute one statement at a time.
Problem
I have a CTE in which a `SELECT` statement is generated, but SQL Server (2012) does not allow calling `EXEC` on it. Here's the query: ``` DECLARE @guidToFind uniqueidentifier = 'E4069560-091A-4026-B519-104F1C7693B3'; WITH GuidCols (TableName, ColName, Query) As ( SELECT C.TABLE_NAME, C.COLUMN_NAME, 'SELECT ' + QUOTENAME(C.TABLE_NAME) + '.' + QUOTENAME(C.COLUMN_NAME) + ' FROM ' + QUOTENAME(C.TABLE_NAME) + ' WHERE ' + QUOTENAME(C.COLUMN_NAME) + ' = ''' + cast(@guidToFind AS VARCHAR(50))+ '''' FROM INFORMATION_SCHEMA.COLUMNS C INNER JOIN INFORMATION_SCHEMA.TABLES T ON C.TABLE_NAME = T.TABLE_NAME AND T.TABLE_TYPE = 'BASE TABLE' WHERE C.DATA_TYPE = 'uniqueidentifier' ) -- SELECT * FROM EXEC( GuidCols.Query ) ``` The problem is not solve if I uncomment the `SELECT` statement after CTE. The purpose of this query is to find all instances of a GUID in a database. Currently I'm solving this problem using a script like this. However I would be happier to solve this problem without iterating through rows, using set operations and other techniques.