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 )

Solution 1

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

Solution 2

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;

Solution 3

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).

Solution 4

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 

View more solutions

Comments

[Lucero]

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]

@Aaron I just try to be precise with the terminology.

[MikeTeeVee]

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!

[Derek Greer]

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.

[user1829319]

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]

Its actually sql server 2016

[bluelurker]

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.

[Golden Lion]

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.

[Fooker]

Not working, getting error 'A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.'

[Arun Prasad E S]

@bluelurker I think some one with time needs to give a better opinion on this. waiting for that.

[Panda1122]

@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.

[Arun Prasad E S]

@Panda1122 how to be sure, that this is the case. any reference.

[Panda1122]

@ArunPrasadES: Profiler doesnt help..?