gigi gigi - 7 months ago 11
SQL Question

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 )

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