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

Accepted Answer

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
```

Use one CTE many times

Solution

Problem