SQL Server Performance

Can CTEs be called again in SP Scope..

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Ritu, Mar 19, 2008.

  1. Ritu New Member

    Hi,
    Can CTEs be called again and again with in SP Scope. See Code Follows:
    Create Proc TestCTEAS
    Begin
    With DataSet(EmployeeID,RowNumber)
    As
    (Select Employeeid,Row_Number() Over (Order by Employeeid asc) as RowNumber from employee
    where DateOfJoining < '12-Jan-2008')
    Select Top 1 * from DataSet; -- This Works FineSelect Top 5 * from DataSet; --- This Line gives Error "DataSet is Invalid Object"End
    Ritu, Mar 19, 2008
    #2

  2. FrankKalis Moderator

    From BOL:
    "A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns. A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view."
    So, what you are trying to do, is invalid. However, you might use a view and use this in your procedure.
    FrankKalis, Mar 20, 2008
    #3

  3. Adriaan New Member

    Yep, I too was expecting CTEs to be more like recordset objects, but they're not. Bummer.[:/]
    Adriaan, Mar 21, 2008
    #4

  4. Madhivanan Moderator

    The only case you can use the cte more than once is when you use UNION, UNION ALL, JOIN,etc
    select columns from mycte where......
    union all
    select columns from mycte where......
    Madhivanan, Apr 2, 2008
    #5

  5. EMoscosoCam Member

    However, suppose that the same SELECT is issued in the procedure: The engine is smart enough to retrieve the data from a memory cache, right?
    EMoscosoCam, Apr 23, 2008
    #6

  6. FrankKalis Moderator

    [quote user="EMoscosoCam"]
    However, suppose that the same SELECT is issued in the procedure: The engine is smart enough to retrieve the data from a memory cache, right?
    [/quote]
    Yes, the data is very likely to be retrieved from cache.
    FrankKalis, Apr 23, 2008
    #7

  7. jaybee New Member

    Guys, what's a CTE? Also, was the 'With' statement (which is self-explanatory) introduced in SS2K5?
    jaybee, Jun 3, 2008
    #8

  8. Madhivanan Moderator

    Madhivanan, Jun 4, 2008
    #9

Share This Page