同一のストアドプロシージャ内で同じテーブルを複数のSQLステートメントで使用することがあります。
テーブル名が長い時には、別名を定義することが多いのですが、SQLステートメント毎に別名を指定するのは面倒です。
このような場合には、同一のストアドプロシージャ内であれば利用できる別名定義の宣言を利用します。
この宣言が共通テーブル式です。

【構文】
WITH [別名] AS
(
SELECT 項目1, 項目2 ...
FROM テーブル名
)
SQLステートメント(SELECT, INSERT, UPDATE, DELETE)

以下、サンプル。
《環境》
・売上テーブル(Sales)
 項目(伝票番号(No), 売上日(SalesDate), 商品ID(ArticleID), 単価(UnitPrice), 数量(Count))
 ※SalesDateは、文字列で"YYYYMMDD"の形式でデータが保存されているものとします。
・商品テーブル(Articles)
 項目(商品ID(ArticleID), 商品名(Name))

利用するテーブルの作成用スクリプトは、こちら(SampleCTE.zip)からダウンロードできます。
データは、適当に入力して下さい。

・取得する結果
 (月, 商品コード, 商品名, 当年売上, 前年売上)

《コード》
--前年度と今年度の同月の売上データを取得
--対象年月を指定

Declare @Month char(2)
Set @Month = '04'
Declare @ThisYear char(4)
Set @ThisYear = '2008'
Declare @LastYear char(4)
Set @LastYear = '2007';

WITH MonthlySales AS
(
SELECT SubString(SalesDate, 1, 6) As YearMonth,
ArticleID,
SUM(UnitPrice * Count) As TotalPrice
FROM Sales
GROUP BY SubString(SalesDate, 1, 6),
ArticleID
)
SELECT @Month As Month,
a.ArticleID,
a.Name,
SUM(ThisYear.TotalPrice) As ThisYearTotal,
SUM(LastYear.TotalPrice) As LastYearTotal
FROM Articles a
LEFT OUTER JOIN MonthlySales ThisYear
ON a.ArticleID = ThisYear.ArticleID
AND ThisYear.YearMonth = @ThisYear + @Month
LEFT OUTER JOIN MonthlySales LastYear
ON a.ArticleID = LastYear.ArticleID
AND LastYear.YearMonth = @LastYear + @Month
GROUP BY a.ArticleID,
a.Name

上記は、共通テーブル式を使わないと、以下のようになります。
--前年度と今年度の同月の売上データを取得
--対象年月を指定

Declare @Month char(2)
Set @Month = '04'
Declare @ThisYear char(4)
Set @ThisYear = '2008'
Declare @LastYear char(4)
Set @LastYear = '2007';

SELECT @Month As Month,
a.ArticleID,
a.Name,
SUM(ThisYear.TotalPrice) As ThisYearTotal,
SUM(LastYear.TotalPrice) As LastYearTotal
FROM Articles a
LEFT OUTER JOIN
(SELECT SubString(SalesDate, 1, 6) As YearMonth,
ArticleID,
SUM(UnitPrice * Count) As TotalPrice
FROM Sales
GROUP BY SubString(SalesDate, 1, 6),
ArticleID) ThisYear
ON a.ArticleID = ThisYear.ArticleID
AND ThisYear.YearMonth = @ThisYear + @Month
LEFT OUTER JOIN
(SELECT SubString(SalesDate, 1, 6) As YearMonth,
ArticleID,
SUM(UnitPrice * Count) As TotalPrice
FROM Sales
GROUP BY SubString(SalesDate, 1, 6),
ArticleID) LastYear
ON a.ArticleID = LastYear.ArticleID
AND LastYear.YearMonth = @LastYear + @Month
GROUP BY a.ArticleID,
a.Name

【補足事項】
・異なるストアドプロシージャの場合、共通テーブル式は使えません。
・WITH句を利用する場合、直前のSQLステートメントの最後に「;」(セミコロン)を付ける必要があります。
・SQL Server 2005で追加された機能です。

【参考サイト】
・Microsoft MSDNライブラリ
 共通テーブル式の使用