John Baird
Coatesville, Pennsylvania, United States
General information
Forum:
Microsoft SQL Server
Environment versions
SQL Server:
SQL Server 2012
>>Is there a way to have a Common Table Expression valid for more than one command in the script? Presently, I have to repeat the same command again, later down the script, so it would be available for other commands.
>
>
>As Naomi and Borislav said, you can't. The options are to redirect to a temp table, table variable, etc. if you need to use it multiple times. In that instance, it's questionable if a CTE is really necessary - you might not be gaining anything (unless you were using the CTE for recursion).
>
>In case you're curious, the reason you can't is this....a CTE is really just syntax sugar - it allows you to take the syntax for a derived table subquery and place it "above" the outer query that uses it. If you actually try to run the CTE code itself without executing the subsequent line that uses it, you'll get an error.
>
>So the database engine is really just substituting the reference to the CTE in the main query with the SELECT statement in the CTE. Sort of like expanding a macro. So it's all gone after the line of code that uses the CTE.
If its a matter of code duplication, can you use them inside a function to return the data. That way he could just call the function instead of duplication sql code.
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only