John Baird
Coatesville, Pennsylvanie, États-Unis
Information générale
Forum:
Microsoft SQL Server
Versions des environnements
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.
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement