Use one CTE many times

Question

I have this, and i get an error at set total. Why can't i access a cte many times?

ALTER PROCEDURE [dbo].[GetLeaguePlayers]
(
    @idleague int,
    @pageNumber int,
    @pageSize int,
    @total int OUTPUT
)
AS
WITH CTEPlayers AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY p.Name) AS RowNumber, p.Id, p.Name, t.Name AS Team
    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
)
SELECT Id, Name
FROM CTEPlayers c
WHERE RowNumber>@pageSize*(@pageNumber-1) AND RowNumber<@pageSize*@pageNumber;
SET @total = ( SELECT COUNT(*) FROM CTEPlayers )

JNK · Accepted Answer · 2012-04-17T18:32:00.127

109

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

edited Apr 17 '12 at 18:32

answered Apr 17 '12 at 18:26

JNK

63,321
15
122
138

26

CTEs are not restricted to a single query, but to a single statement. You can have multiple queries use the same CTE (if nested, in other CTEs, etc.). – Lucero Apr 17 '12 at 18:29
2

@Aaron I just try to be precise with the terminology. – Lucero Apr 17 '12 at 18:31
I used temp tables to persist the query. if OBJECT_ID('tempdb..#myTempTable') is not null Drop Table #myTempTable as clean up after creating a temp table. You can select field1 into #myTempTable from aTable to create the schema. – Golden Lion Mar 12 '20 at 22:40

om471987 · Answer 2 · 2016-12-07T21:31:25.310

27

None of the above answers are correct... You can execute CTE once and achieve the result you want.. here is the query

ALTER PROCEDURE [dbo].[GetLeaguePlayers]
(
    @idleague int,
    @pageNumber int,
    @pageSize int,
    @total int OUTPUT
)
AS
WITH CTEPlayers AS
(
    SELECT p.Id, p.Name, t.Name AS Team
    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
),
TotalCount AS
(
 SELECT COUNT(*) AS Total FROM CTEPlayers
),
Final_Result AS
(
 SELECT ROW_NUMBER() OVER (ORDER BY p.Name) AS RowNumber, p.Id, p.Name, t.Name AS Team,
  (SELECT Total FROM TotalCount) AS Total
    FROM CTEPlayers
)
SELECT Id, Name, @total = Total
FROM Final_Results c
WHERE RowNumber>@pageSize*(@pageNumber-1) AND RowNumber<@pageSize*@pageNumber;

edited Dec 07 '16 at 21:31

answered Dec 07 '16 at 20:40

om471987

5,398
5
32
40

2

It seems this is restricted to read-only operations. Attempting to update the same result set is giving me errors on a valid update statement based upon a join with the first CTE. – Derek Greer May 19 '17 at 19:08
I get this error doing so: "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations." SQL Server 2014 though. – user1829319 Oct 20 '17 at 05:44
1

Its actually sql server 2016 – user1829319 Oct 20 '17 at 06:07
1

Not working, getting error 'A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.' – Fooker May 14 '21 at 13:04

Lucero · Answer 3 · 2012-04-17T18:35:29.050

20

A CTE is, per definition, only valid for one statement.

You can create an inline table-valued function and then use this as often as you like. The inline function does what the name suggest; its query gets to be part of the query using it (in contrast to non-inline functions which are executed separately and used as a rowset).

edited Apr 17 '12 at 18:35

answered Apr 17 '12 at 18:25

Lucero

59,176
9
122
152

1

As a software developer, I prefer this approach. It allows me to consolidate my logic into one function and then use it across multiple stored procedures. It is especially helpful for complex queries. I have found that I can return a bunch of columns and add a lot of joins to make it reusable, however may not be needed for every sproc that references it, but sql-server takes care not to process the extra joins and columns if your sproc isn't using them. You can also have ITVF's (Inline Table-Valued Functions) call others ITVF's so you can build upon your base query logic even further! – MikeTeeVee Sep 11 '12 at 18:54

Arun Prasad E S · Answer 4 · 2020-01-10T17:51:32.930

Using CTE Multiple Times to collect Data

;with CTEReminder AS
(
    Select r.ReminderID,r.IsVerificationRequired from ReminderTbl r      -- main table
),
FileTaskCountTempTbl   as     
    (
        select  COUNT(t.ReminderID) as FileTaskCount                     -- getting first result
            from TaskTbl t
                left join CTEReminder r on t.ReminderID = r.ReminderID          
    ),
FollowUpCountTempTbl  as
    (
        select COUNT(f.FollowUpID)  as Total                             -- getting second result
            from FollowUpTbl f              --cte not used here
    ),
MachineryRegularTaskCountTempTbl as
    (
        select  COUNT(t.ReminderID) as TotalCount                        -- getting third result
                from TaskTbl t
                    left join CTEReminder r on t.ReminderID = r.ReminderID                  
    ),
FinalResultTempTbl as
    (
        select COUNT(t.ReminderID)  as MachineryTaskCount,               -- getting fourth result
                (select * from MachineryRegularTaskCountTempTbl ) as MachineryRegularTaskCount,  -- Combining earlier results to last query 
                (select * from FollowUpCountTempTbl ) as FollowUpCount,   -- Combining earlier results to last query 
                (select * from FileTaskCountTempTbl ) as FileTaskCount   -- Combining earlier results to last query 
            from TaskTbl t
                left join CTEReminder r on t.ReminderID = r.ReminderID          
    )

select * from FinalResultTempTbl

is there a known performance impact with such an approach? I have observed that if one CTE returns a lot of records, even though if the execution time is fast, due to longer fetch time, the next CTE doesn't perform really well. I tested this by LIMITING the result in second CTE to one row. — bluelurker, Feb 17 '20 at 11:52
@bluelurker I think some one with time needs to give a better opinion on this. waiting for that. — Arun Prasad E S, Dec 27 '21 at 10:30
@bluelurker : A CTE is just like macro which is expanded into the query. Which means that if you refer to the CTE multiple times in the query, the CTE will be computed multiple times. — Panda1122, Jan 11 '22 at 08:14
@Panda1122 how to be sure, that this is the case. any reference. — Arun Prasad E S, Jan 11 '22 at 12:57

score 0 · Answer 5 · edited Jul 11 '16 at 23:00

In this case, I use this:

ALTER PROCEDURE [dbo].[GetLeaguePlayers]
(
 @idleague int,
 @pageNumber int,
 @pageSize int,
 @total int OUTPUT
)
AS

WITH CTEPlayers AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY p.Name) AS RowNumber,    
        COUNT(1) OVER () AS RecordCount,
    p.Id, p.Name,   
    t.Name AS Team
    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
)

SELECT RowNumber,
    CAST(CEILING(CAST(RecordCount AS FLOAT) / CAST(@pageSize AS FLOAT)) AS INT) PageCount,
    RecordCount,
    Id, 
    Name
FROM CTEPlayers c
WHERE RowNumber > @pageSize*(@pageNumber-1) AND RowNumber < @pageSize*@pageNumber;

score 0 · Answer 6 · edited May 23 '20 at 13:05

Store the output in temporary table along-with the total count; set the output variable value and return the required columns from temporary table

ALTER PROCEDURE [dbo].[GetLeaguePlayers]
(
    @idleague int,
    @pageNumber int,
    @pageSize int,
    @total int OUTPUT
)
AS
WITH CTEPlayers AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY p.Name) AS RowNumber, p.Id, p.Name, t.Name AS Team
    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
),
TotalCounter(TotalRecords) as
(select count(1) from CTEPlayers)


SELECT Id, Name, TotalRecords(select TotalRecords from TotalCounter) into #tmp
FROM CTEPlayers c
WHERE RowNumber>@pageSize*(@pageNumber-1) AND RowNumber<@pageSize*@pageNumber;

SET @total = ( SELECT TotalRecords FROM #tmp)

select Id, Name from $tmp

drop table #tmp

Use one CTE many times

6 Answers6

Linked

Related