title: Use one CTE many times
body: <p>I have this, and i get an error at set total.
  Why can't i access a cte many times?</p>
  
  <pre><code>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&gt;@pageSize*(@pageNumber-1) AND RowNumber&lt;@pageSize*@pageNumber;
  SET @total = ( SELECT COUNT(*) FROM CTEPlayers )
  </code></pre>