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
  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.
  3. Adriaan New Member

    Yep, I too was expecting CTEs to be more like recordset objects, but they're not. Bummer.[:/]
  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......
  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?
  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.
  7. jaybee New Member

    Guys, what's a CTE? Also, was the 'With' statement (which is self-explanatory) introduced in SS2K5?
  8. Madhivanan Moderator

Share This Page