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.
You can not use
exec as a part of a
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.
We've highlighted the inner part of the CTE query, which we could execute. In this case, it would generate the same output. Your AlmondValue Common Table Expressions (CTE) have two types, recursive and non-recursive. We will see how the recursive CTE works with examples in this tip. A recursive CTE can be explained in three parts: Anchor Query: This is the first statement which is executed. This query will give the base data for the CTE.
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:
- Your statement is parsed.
- An execution plan is generated.
- 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.
If that predicate returns no rows, they wanted to run another query using a InitPlan 2 (returns $1) -> CTE Scan on r r_1 (cost=0.00..0.18 rows=9 width=0) Here, I've finally created a benchmark that anonymises the results You can also use a CTE in a CREATE a view, as part of the view’s SELECT query. In addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement. Using the CTE – We can define CTEs by adding a WITH clause directly before SELECT, INSERT, UPDATE, DELETE, or MERGE statement.
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/
joins and subqueries. Recursive CTE's can solve complex queries. Notice it contains a query that can be run on its own in SQL. This is called the CTE query In general, a recursive CTE has three parts: An initial query that returns the base result set of the CTE. The initial query is called an anchor member. A recursive query that references the common table expression, therefore, it is called the recursive member. The recursive member is union-ed with the anchor member using the UNION ALL operator.
You may use OPENQUERY instead of EXEC:
SELECT * FROM OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE name = ''NewTitle''');
When executing a CTE, any hints that reference a CTE may conflict with other hints The recursive CTE definition must contain at least two CTE query values for both the parent and child columns, an infinite loop is created. First, we defined cte_sales_amounts as the name of the common table expression. the CTE returns a result that that consists of three columns staff, year, and sales derived from the definition query. Second, we constructed a query that returns the total sales amount by sales staff and year by querying data from the orders, order_items and staffs
The SELECT statement in your CTE query must follow the same After you've run your statement, the CTE result set is not available to other statements. Note that a CTE created incorrectly could enter an infinite loop. I had a situation of returning ROW_NUMBER variable value for my paging query in SQL, for this I used CTE. A common table expression is actually a temporary result set or a table whose scope is defined or limited to the current statement.
After you've run your statement, the CTE result set is not available to other statements. hierarchical data as the CTE continues to execute until the query returns the entire hierarchy. If a CTE is created incorrectly it can enter an infinite loop. Try using your first query to open a cursor, then within the loop execute the result string as dynamic SQL.
Download: Exec query in cte Recursive CTEs powered by Peatix : More than so very close now, instead of using a cte I created the temp table as suggested. I have to write a dynamic pivot based on a complex query and I want to use a common table expression to create the dataset on which I have to build the pivot to keep it outside the dynamic sql and have it compiled. My problem is that I don't know if i can use the CTE in a SET where I wrap the dynamic SQL I have to execute. let see the code: