CODEWITHSUNDEEP
sqlsql-servercommon-table-expression
Sina Iravanian
Sina Iravanian

Reputation: 16296

How to EXEC a query generated in a CTE

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.

Upvotes: 6

Views: 18627

Answers (4)

José Ricardo Júnior
José Ricardo Júnior

Reputation: 488

You may use OPENQUERY instead of EXEC:

SELECT * FROM OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE name = ''NewTitle''');

Reference: https://learn.microsoft.com/en-us/sql/t-sql/functions/openquery-transact-sql?view=sql-server-2017

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138970

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.

Upvotes: 5

NG.
NG.

Reputation: 6053

Exec executes a T-SQL command that you embed in its parentheses. The difference is that the EXEC() function can execute a string or string variable, as you saw in the preceiding code. The argument for EXEC() can be a literal string, but the real power of EXEC() comes to light when you use a variable.

It executes the command contained in the variable, but in a different context from the session calling the function. That's important, because it introduces some severe constraints. Any tables you create are visible to the EXEC() context, but variables declared by the calling session aren't visible to the EXEC() context, and vice versa.

I found an interesting article related to this at : http://msdn.microsoft.com/en-us/library/aa175921(v=sql.80).aspx

Although above is self sufficient, you can also go through : http://blog.sqlauthority.com/2007/09/13/sql-server-difference-between-exec-and-execute-vs-exec-use-execexecute-for-sp-always/

Upvotes: 1

Glen Hughes
Glen Hughes

Reputation: 4822

The EXEC() function is available from T-SQL, but it is not accessible from within a SQL statement. I would assume this is the case because SQL is executed in several steps... something like:

  1. Your statement is parsed.
  2. An execution plan is generated.
  3. The execution plan is run against the database and results are returned.

The EXEC() function is designed to dynamically execute a statement, so in order to do what you're asking, SQL Server would have to perform the first two steps, then while executing the statement, it would have to return back to the first step to parse/execute the dynamic statement. This would have to be repeated for each row in your results.

I assume this type of 'feature' is not available because it could adversely affect the performance and complexity of the db engine; so they leave it to us to execute the first statement, then loop through each record to execute the dynamic statements individually.

Upvotes: 1

Related Questions