Use one CTE many times

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 )

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

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;

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

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 

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;

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