Use one CTE many times

cte sql
multiple cte sql
derived tables offer better performance than ctes.
sql temp table
cte can be self-referencing
one cte after another
cte vs temp table
derived table vs cte

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 )

How Many Times Does a CTE run, On an expensive CTE called multiple times then materialize to #temp is the ( and Microsoft recommends it) to use them after every statement. 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. For storing results and use them again on other queries you have to go for either temp tables or table variables.

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
)
AS
WITH 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 = Total
FROM Final_Results c
WHERE RowNumber>@pageSize*(@pageNumber-1) AND RowNumber<@pageSize*@pageNumber;

CTE use multiple time in single Store Procedure - MSDN, Please advise best way for use multiple times use cte in store procedure. Regards,. Vishnu Dalwadi. vishnu dalwadi. Friday, August 5, 2011 8:58� To use multiple CTE’s in a single query you just need to finish the first CTE, add a comma, declare the name and optional columns for the next CTE, open the CTE query with a comma, write the query, and access it from a CTE query later in the same query or from the final query outside the CTEs.

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

Common Table Expressions (CTEs), 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. Use the key word WITH once at the top. If any of your Common Table Expressions (CTE) are recursive (rCTE) you have to add the keyword RECURSIVE at the top once also, even if not all CTEs are recursive:

Using CTE Multiple Times to collect Data

;with CTEReminder AS
(
    Select r.ReminderID,r.IsVerificationRequired from ReminderTbl r      -- main table
),
FileTaskCountTempTbl   as     
    (
        select  COUNT(t.ReminderID) as FileTaskCount                     -- getting first result
            from TaskTbl t
                left join CTEReminder r on t.ReminderID = r.ReminderID          
    ),
FollowUpCountTempTbl  as
    (
        select COUNT(f.FollowUpID)  as Total                             -- getting second result
            from FollowUpTbl f              --cte not used here
    ),
MachineryRegularTaskCountTempTbl as
    (
        select  COUNT(t.ReminderID) as TotalCount                        -- getting third result
                from TaskTbl t
                    left join CTEReminder r on t.ReminderID = r.ReminderID                  
    ),
FinalResultTempTbl as
    (
        select COUNT(t.ReminderID)  as MachineryTaskCount,               -- getting fourth result
                (select * from MachineryRegularTaskCountTempTbl ) as MachineryRegularTaskCount,  -- Combining earlier results to last query 
                (select * from FollowUpCountTempTbl ) as FollowUpCount,   -- Combining earlier results to last query 
                (select * from FileTaskCountTempTbl ) as FileTaskCount   -- Combining earlier results to last query 
            from TaskTbl t
                left join CTEReminder r on t.ReminderID = r.ReminderID          
    )

select * from FinalResultTempTbl 

Non Recursive CTEs Explained and Why to Use Them, By the time we're done, you'll see how to use one or more non recursive CTEs in a statement to A CTE can be referenced multiple times from a calling query. We also use CTE in the queries that contain analytic functions (or window functions) SQL Server CTE examples. Let’s take some examples of using common table expressions. A) Simple SQL Server CTE example. This query uses a CTE to return the sales amounts by sales staffs in 2018:

In this case, I use this:

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,    
        COUNT(1) OVER () AS RecordCount,
    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 RowNumber,
    CAST(CEILING(CAST(RecordCount AS FLOAT) / CAST(@pageSize AS FLOAT)) AS INT) PageCount,
    RecordCount,
    Id, 
    Name
FROM CTEPlayers c
WHERE RowNumber > @pageSize*(@pageNumber-1) AND RowNumber < @pageSize*@pageNumber;

SQL Server Common Table Expressions (CTE), To view the result, we will use a select query to display our CTE result. a recursive query and can be used to reference itself multiple times. Following is one more way by creating a view: create view vmMyView1 AS with cte as (select 1 as n,2 as m) select * from cte select * from vmMyView1. Please note that these all will vary depending on requirement and how we are going to implement. And you there is no need to go for CTE, I can create view for the query and use it all the required

Using Common Table Expression Result Multiple Time, Good Question, it made me to think for few mins :) . Following is the simple solution by using temporary tables: with cte as ( select 1 as n,2 as m ) I know that your example is not meant to be recursive, but I need recursion on just one of my CTE's, which depends on another CTE's. – FrenkyB Mar 17 '15 at 14:54 1

SQL Server - Multiple CTE in One SELECT Statement Query, I have previously written many articles on CTE. One question I get often is how to use multiple CTE in one query or multiple CTE in SELECT� A neuropathologist has examined the brains of 111 N.F.L. players — and 110 were found to have C.T.E., the degenerative disease linked to repeated blows to the head.

Mastering Common Table Expression or CTE in SQL Server, This tutorial shows you how to use the common table expressions or CTE in SQL Server to C) Using multiple SQL Server CTE in a single query example. USE Library; -- list authors with the number of books they've written WITH cteBooksByAuthor AS ( SELECT AuthorId, COUNT(*) AS CountBooks FROM tblBook GROUP BY AuthorId ) -- use this CTE to show authors who have written -- more than 1 book SELECT a.FirstName + ' ' + a.LastName AS Author, cte.CountBooks AS 'Number of books' FROM cteBooksByAuthor AS cte INNER JOIN tblAuthor AS a ON cte.AuthorId=a

Comments
  • NO! Do *NOT change your question! Go ask another one instead.
  • CTEs are not restricted to a single query, but to a single statement. You can have multiple queries use the same CTE (if nested, in other CTEs, etc.).
  • @Aaron I just try to be precise with the terminology.
  • I used temp tables to persist the query. if OBJECT_ID('tempdb..#myTempTable') is not null Drop Table #myTempTable as clean up after creating a temp table. You can select field1 into #myTempTable from aTable to create the schema.
  • It seems this is restricted to read-only operations. Attempting to update the same result set is giving me errors on a valid update statement based upon a join with the first CTE.
  • I get this error doing so: "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations." SQL Server 2014 though.
  • Its actually sql server 2016
  • As a software developer, I prefer this approach. It allows me to consolidate my logic into one function and then use it across multiple stored procedures. It is especially helpful for complex queries. I have found that I can return a bunch of columns and add a lot of joins to make it reusable, however may not be needed for every sproc that references it, but sql-server takes care not to process the extra joins and columns if your sproc isn't using them. You can also have ITVF's (Inline Table-Valued Functions) call others ITVF's so you can build upon your base query logic even further!
  • is there a known performance impact with such an approach? I have observed that if one CTE returns a lot of records, even though if the execution time is fast, due to longer fetch time, the next CTE doesn't perform really well. I tested this by LIMITING the result in second CTE to one row.