Results 1 to 5 of 5

Thread: Cte

  1. #1
    Join Date
    May 2009


    I want steps for cte...

    Thanx in advance

  2. #2
    Join Date
    Sep 2002
    What's cte?

  3. #3
    Join Date
    May 2009


    Only thing I know about CTE is,Common Table Expression,,Such like an Stored Procedure....Bt I want to know the steps to create it,where to create it,and how to call in c#...

  4. #4
    Join Date
    Apr 2009
    MDS, A CTE (common table expression as you stated) is part of an SQL statement. The basic format is:

    WITH cte-name (col datatype, col datatype, etc.)
    AS( sql-statement)
    , cte-name1 (col datatpe, col datatype, etc.)
    AS( sql-statement)
    SELECT cols
    FROM table-name
    , cte-name1
    WHERE ...

    1) col /datatypes are usually not needed for a CTE but are nice to include anyway.
    2) You can have 1 or many CTE defined. I used 2 in the above example but it could be 1 or 3 or many.
    3) A CTE can reference a previous defined CTE but not one after it. In the above example, cte-name1 could refer to cte-name in the FROM but cte-name could NOT refer to cte-name1.
    4) Once a CTE is defined it can be referenced in a FROM (of the same SQL statement) 1 or many times.
    5) In the Select statement referencing the CTE you don't have to reference other tables. In the example above, the FROM could be just FROM cte-name.

    A CTE is NOT a stored procedure. It is just an extension of an SQL Statement. As such it would go in a program where normal SQL would go.

  5. #5
    Join Date
    Jun 2009
    South Florida
    CREATE TABLE #subject(id int PRIMARY KEY IDENTITY(1,1),parent char(1) NULL, child char(1))
    INSERT INTO #subject(child) VALUES ('A')
    INSERT INTO #subject(parent, child) VALUES('A', 'B')
    INSERT INTO #subject(parent, child) VALUES('B', 'C')
    INSERT INTO #subject(parent, child) VALUES('C', 'D')
    INSERT INTO #subject(parent, child) VALUES('D','E');

    WITH subj(parent, child, Level)
    -- Anchor member definition
    SELECT s.parent, s.child, 0 AS level
    FROM #subject s
    WHERE parent IS NULL
    -- Recursive member definition
    SELECT s.parent, s.child,level + 1
    FROM #subject s
    INNER JOIN subj sj
    ON s.parent = sj.child
    -- Statement that executes the CTE
    SELECT parent, child, Level
    FROM subj

    DROP TABLE #subject

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts