WITH (CTE : Common Table Expression) 사용 예제

Checked : MSSQL2008.

 

WITH cte_Table (MID)
AS
(
    SELECT CAST(MID AS INT) AS 'MID'
        FROM IT_MASTER (NOLOCK)
)
SELECT CASE
                WHEN (CTE.MID < 10)
                    THEN ('00' + CAST(CTE.MID AS NCHAR(1)))
                WHEN (CTE.MID < 100)
                    THEN ('0' + CAST(CTE.MID AS NCHAR(2)))
                WHEN (CTE.MID < 1000)
                    THEN (CAST(CTE.MID AS NCHAR(3)))
                ELSE '000' END
    FROM cte_Table AS CTE;