locked
Problem with temparary tables working with CTE's(Common table Expression) RRS feed

  • Question

  • Hi,

    I have some problem with following Stored procedure.

    Create procedure Sp_Test( @ Some params)
    {

    Begin

    -- It creates one Temp table(name is TestEntries) using Common Table Expression

    With TestEnties as (select statement with three tables with joins and subqurires)

    -- Now i going to use the temp table.

    Select * from TestEntries ( with some condition)

    -- After that the Temp automatically deleted from database after this statement. right !!

    -- Again i needed the same temp table for second time to know that rows means count

    Select count(*) from TestEntries

    -- But here it not allowed to perform that stmt. Right.

    -- but how to use that table for the second time and is there any way to solve this type problem

    END

    }

    Actually the above problem is solved by creating one View and performing all operations on that view and at last we drop the view.

    But creating the view and drop the view it causes some performance issues. right.

    Now i want to know how to solve the above problem with creating the any databse objets.

    Plz help me if any one knows.............

    thanks in advance............

    pavan.
    Sunday, January 24, 2010 5:52 AM

Answers

  • You can do assigment into local variables in the main statement, but you cannot mix them with regular SELECT query. Demo follows.

    DECLARE  @TotalOrders INT, 
             @YearlyAvg   INT; 
    
    WITH cteCount 
         AS (SELECT TotalOrderCount = COUNT(* ) 
             FROM   Sales.SalesOrderHeader), 
         cteYYYY 
         AS (SELECT   YEAR = YEAR(OrderDate), 
                      YearlyOrders = COUNT(* ) 
             FROM     Sales.SalesOrderHeader 
             GROUP BY YEAR(OrderDate)) 
    SELECT @TotalOrders = (SELECT TotalOrderCount 
                           FROM   cteCount), 
           @YearlyAvg = AVG(YearlyOrders) 
    FROM   cteYYYY 
    
    SELECT @TotalOrders, 
           @YearlyAvg 
           -- 31465  7866 

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    • Marked as answer by Zongqing Li Friday, January 29, 2010 8:14 AM
    Thursday, January 28, 2010 1:09 AM

All replies

  • You can add count(*) right inside this CTE. In other words,

    ;with cte as (select ..., count(*) over () as TotalCount from ....)

    select * from cte where some condition

    You have totalcount as part of your select statement.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Sunday, January 24, 2010 5:58 AM
  • hey Noom,

    with cte as (select ..., count(*) over () as TotalCount from ....)

    ths stmt is not executed yaar........because Count(*) return only one row and rest of stmt returns Multiple rows........Right. on that time how it is executed yaar.........

    But our requirement is i have that assigned that Count(*) value to one out parameter.

    I think it is not possible.......Right.

    Tell me u have another solution.........

    Thanks

    Pavan
    Sunday, January 24, 2010 6:20 AM
  • No, that's the only solution I know if we don't want to store the result in the temp table.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, January 27, 2010 11:40 PM

  • with cte as (select ..., count(*) over () as TotalCount from ....)

    ths stmt is not executed yaar........because Count(*) return only one row and rest of stmt returns Multiple rows........Right. on that time how it is executed yaar.........

    You can have multiple CTE-s even nested CTE-s within one statement.

    Demo follows.

    -- SQL Server 2008 T-SQL multiple CTE-s query
    ;WITH cteCount AS ( SELECT TotalOrderCount=COUNT(*) FROM Sales.SalesOrderHeader),
    cteYYYY AS (SELECT YEAR=YEAR(OrderDate), YearlyOrders = COUNT(*)
                FROM Sales.SalesOrderHeader GROUP BY YEAR(OrderDate))
    SELECT YEAR, 
     Percentile=CONVERT(decimal(4,1),100.0 * YearlyOrders/
                            (SELECT TotalOrderCount FROM cteCount)) 
    FROM cteYYYY 
    ORDER BY YEAR
    /*
    YEAR	Percentile
    2001	4.4
    2002	11.7
    2003	39.5
    2004	44.3
    */

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Thursday, January 28, 2010 12:10 AM
  • It still would not help us to get the total count into a separate variable.

    BTW, Kalman, check your e-mail.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, January 28, 2010 12:25 AM
  • You can do assigment into local variables in the main statement, but you cannot mix them with regular SELECT query. Demo follows.

    DECLARE  @TotalOrders INT, 
             @YearlyAvg   INT; 
    
    WITH cteCount 
         AS (SELECT TotalOrderCount = COUNT(* ) 
             FROM   Sales.SalesOrderHeader), 
         cteYYYY 
         AS (SELECT   YEAR = YEAR(OrderDate), 
                      YearlyOrders = COUNT(* ) 
             FROM     Sales.SalesOrderHeader 
             GROUP BY YEAR(OrderDate)) 
    SELECT @TotalOrders = (SELECT TotalOrderCount 
                           FROM   cteCount), 
           @YearlyAvg = AVG(YearlyOrders) 
    FROM   cteYYYY 
    
    SELECT @TotalOrders, 
           @YearlyAvg 
           -- 31465  7866 

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    • Marked as answer by Zongqing Li Friday, January 29, 2010 8:14 AM
    Thursday, January 28, 2010 1:09 AM
  • Right - in other words, we can not get both in one query.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, January 28, 2010 1:13 AM
  • Thnks yaar...........

    the problem is resolved..........
    Thursday, January 28, 2010 10:32 AM
  • ok........

    Thnks yaar.............
    Thursday, January 28, 2010 10:32 AM