Menu
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 ) 
433
Unlike a derived table, a CTE behaves more like an in-line view and can be referenced multiple times in the same query. Using a CTE makes complex queries easier to read and maintain. Because a CTE can be referred to multiple times in a query, syntax can be simpler.
The CTE runs only once. The Nested Loops operator splits every row into four. And the statistics add up: The original query performed around 82,000 logical reads on dbo. Posts and 178,000 logical reads on dbo.
You can't use CTE with multiple queries. Use of CTE or you can say its scope is restricted to its query only. For using in other queries you have to create the same CTE again. To use the same logic again, you can create a VIEW of the CTE and use it again.
YES you can reuse it!
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
