Cte

[MDS]

hi,
I want steps for cte...

Thanx in advance

[rmiao]

What's cte?

[MDS]

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

[SDas]

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

Notes:
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.

[devans561]

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)
AS
(
-- Anchor member definition
SELECT s.parent, s.child, 0 AS level
FROM #subject s
WHERE parent IS NULL
UNION ALL
-- 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