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.IdLeagueWHERE 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)ASWITH 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 = TotalFROM Final_Results cWHERE 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).