Common Table Expression - T SQL

Common table Expression is used to specify a temporarily named results set. Below is an example.


WITH CTE AS
(
SELECT folder_id, folder_parent, folder_foldermask, folder_name
FROM tbl_dmsdocument INNER JOIN tbl_dmsfolder ON tbl_dmsdocument.document_folder = tbl_dmsfolder.folder_id
WHERE document_folder = @DocumentFolder AND document_id = @DocumentID

UNION ALL

SELECT ori.folder_id, ori.folder_parent, ori.folder_foldermask, ori.folder_name
FROM CTE AS newtbl INNER JOIN tbl_dmsfolder AS ori ON newtbl.folder_parent = ori.folder_id
)
SELECT folder_name FROM CTE ORDER BY folder_id


Wanna go crazy with the CTE and join it with another table? ... go ahead !!