Use One Cte Many Times

Use one CTE many times

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

How to reference one CTE twice?

You can use commas to create multiple CTEs that references the CTEs Above.

Just to illustrate what I mean:

with recs as (
select 
    *, 
    row_number() over (order by id) as rownum from ......
    ),
counts as (
    select count(*) as totalrows from recs
)
select recs.*,count.totalrows
from recs
cross apply counts 
where rownum between @a and @b .... 

This is not the a good solution.

The best solution I found to have the total count in a CTE without counting the records is described in this article.

DECLARE @startRow INT; SET @startrow = 50;
WITH cols
AS
(
    SELECT table_name, column_name, 
        ROW_NUMBER() OVER(ORDER BY table_name, column_name) AS seq, 
        ROW_NUMBER() OVER(ORDER BY table_name DESC, column_name desc) AS totrows
    FROM [INFORMATION_SCHEMA].columns
)
SELECT table_name, column_name, totrows + seq -1 as TotRows
FROM cols
WHERE seq BETWEEN @startRow AND @startRow + 49
ORDERBY seq

How I can use CTE table more then one time

You can not use CTE in more than one query. But alternatively you can use it by making another CTE for Sum or Count purpose from existing CTE and cross join them.

see below query.

With CTETable as
(
   select * from Table
), CTETotal As 
(
   SELECT COUNT(count) 'count' 
   from CTETable
) 
Select * from CTETable 
CROSS JOIN CTETotal

SQL Server - How to use one CTE to do multiple queries?

Use TABLE variable and OUTPUT Clause (Transact-SQL)

DECLARE @Deleted TABLE
(
    FirstName VARCHAR(255),
    LastName VARCHAR(255)
)

WITH CTE AS (
    SELECT FirstName, LastName, count(*) FROM Employee
    GROUP BY FirstName, LastName
    HAVING count(*) > 1
)
DELETE FROM SomeOtherTable as sot
OUTPUT deleted.FirstName, deleted.LastName INTO @Deleted
WHERE sot.FirstName = cte.FirstName and sot.LastName = cte.LastName;

INSERT INTO PeopleTable (FirstName,LastName) 
SELECT FirstName, LastName from @Deleted;

Or, even better, as @Hart suggested in the comments insert OUTPUT values straight to another table

WITH CTE AS (
    SELECT FirstName, LastName, count(*) FROM Employee
    GROUP BY FirstName, LastName
    HAVING count(*) > 1
)
DELETE FROM SomeOtherTable as sot
OUTPUT deleted.FirstName, deleted.LastName INTO PeopleTable (FirstName,LastName) 
WHERE sot.FirstName = cte.FirstName and sot.LastName = cte.LastName;

CTE executed multiple times

Common table expressions are not temporary tables, materialized views, or cached result sets. They are just expressions, and they can be evaluated more than once (which means for functions like NEWID(), you will get a new value each time the expression is evaluated). I touch on this in the "Beware the sugar" portion of this post:

Even in very simple cases, you can see that a CTE that accesses a table once, but is referenced multiple times, ends up evaluating the CTE (and hence accessing the underlying table) multiple times.

And address a similar question here:

• Caching intermediary CTEs for multiple uses

Martin Smith has an insightful and thorough answer here:

• Which are more performant, CTE or temporary tables?

I do understand that many people make assumptions about how a CTE works, and I appreciate the value that cached CTEs would provide. That's not how they work today, but you can vote on this feedback item to shape that functionality in the future.

You can also ask for clarification in the official documentation - but generally documentation does not list out all of the things that a feature can't do. A common example is, "why doesn't the documentation explicitly state that SELECT * FROM table without an ORDER BY does not guarantee the output in some specific order?" or, more abstractly, "why doesn't my car's owner manual tell me the car can't fly?"

In the meantime, if you want the same value for NEWID() every time you reference the place it came from, you should use a #temp table, @table variable, or local @variable. In your example the change could be as simple as:

declare @newid uniqueidentifier = NEWID();

with cte(id) as 
(
    select @newid as id
)
select * from cte
union all
select * from cte

Example: db<>fiddle

Use CTE multiple times in a query

Obviously the returning expression runs in its own context so it does not 'see' ct. Try something like this:

with cte as (
select 
 col1,
 col2,
 col3,
 col4
from table_a
) 
insert into table_b (col1, col2) select ct.col1, ct.col2 from cte ct 
returning json_build_object(
 'col1', col1,
 'col2', col2,
 'col3', (select cte.col3 from cte where cte.col1 = table_b.col1 and cte.col2 = table_b.col2),
 'col4', (select cte.col4 from cte where cte.col1 = table_b.col1 and cte.col2 = table_b.col2)
);

Keeping it simple and how to do multiple CTE in a query

You can have multiple CTEs in one query, as well as reuse a CTE:

WITH    cte1 AS
        (
        SELECT  1 AS id
        ),
        cte2 AS
        (
        SELECT  2 AS id
        )
SELECT  *
FROM    cte1
UNION ALL
SELECT  *
FROM    cte2
UNION ALL
SELECT  *
FROM    cte1

Note, however, that SQL Server may reevaluate the CTE each time it is accessed, so if you are using values like RAND(), NEWID() etc., they may change between the CTE calls.

---

Related Topics