Use one CTE many times

by

A CTE is basically a disposable view. It only persists for a single statement, and then automatically disappears.

Your options include:

  • Redefine the CTE a second time. This is as simple as copy-paste from WITH... through the end of the definition to before your SET.

  • Put your results into a #temp table or a @table variable

  • Materialize the results into a real table and reference that

  • Alter slightly to just SELECT COUNT from your CTE:

.

SELECT @total = COUNT(*)
FROM Players p 
INNER JOIN Teams t 
    ON p.IdTeam=t.Id 
INNER JOIN Leagues l 
    ON l.Id=t.IdLeague
WHERE l.Id=@idleague

More Related Contents:

  1. Common Table Expression, why semicolon?
  2. Efficiently convert rows to columns in sql server
  3. How can I get column names from a table in SQL Server?
  4. How can I select the first day of a month in SQL?
  5. Multi-statement Table Valued Function vs Inline Table Valued Function
  6. SQL Server Insert if not exists
  7. Select statement to find duplicates on certain fields
  8. How to set table name in dynamic SQL query?
  9. Difference between CTE and SubQuery?
  10. Update records in table from CTE
  11. Using a Variable in OPENROWSET Query
  12. How to find current transaction level?
  13. EXISTS vs JOIN and use of EXISTS clause
  14. Check for file exists or not in sql server?
  15. Group DateTime into 5,15,30 and 60 minute intervals
  16. Why is a UDF so much slower than a subquery?
  17. Is there a SELECT … INTO OUTFILE equivalent in SQL Server Management Studio?
  18. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  19. Which SQL query is faster? Filter on Join criteria or Where clause?
  20. UPDATE and REPLACE part of a string
  21. Improve SQL Server query performance on large tables
  22. How do I change db schema to dbo
  23. Why & When should I use SPARSE COLUMN? (SQL SERVER 2008)
  24. Count records for every month in a year
  25. Using CASE Statement inside IN Clause
  26. Single or multiple databases
  27. SQL Server: +(unary) operator on non-numeric Strings
  28. How to Select Every Row Where Column Value is NOT Distinct
  29. How to do a case sensitive GROUP BY?
  30. How to automatically generate unique id in SQL like UID12345678?

Leave a Comment