如何在T-SQL函数中正确放置并使用多个WITH子句？

现有WITH子句定义 #

WITH a AS (
    SELECT 
        *
    FROM 
        dbo.mydb
    WHERE 
        condition
),
b AS (
    SELECT 
        * 
    FROM (
        SELECT 
            big switch case   
        FROM 
            dbo.myotherdb
        LEFT JOIN 
            dbo.anotherone
        WHERE 
            condition
),
c AS (
    SELECT 
        a.*
    FROM 
        a
    LEFT JOIN 
        b
)

最初的函数尝试 #

我希望在自定义函数中使用上述的c子句，最初的函数写法如下：

CREATE FUNCTION dbo.MyFunction() RETURNS DECIMAL
AS BEGIN
    DECLARE @MyVariable DECIMAL
    SET @MyVariable = (
                        CASE
                        WHEN ( 
                            SELECT TOP(1) c.something 
                            FROM c AS c 
                            ) IS NOT NULL THEN 1
                        WHEN (
                            SELECT TOP(1) c.something
                            FROM c AS c 
                            WHERE c.something = 2
                            ) IS NOT NULL THEN 2 - dbo.otherfunction()
                        END)
RETURN @MyVariable
END GO

遇到的问题 #

无论将WITH子句放在函数声明前、BEGIN之后还是SELECT语句前，都会出现语法错误。

最终解决方案 #

经过调整，正确的写法是将WITH子句放在变量声明之后、赋值查询之前，具体如下：

CREATE FUNCTION dbo.MyFunction() RETURNS DECIMAL
AS BEGIN
    DECLARE @MyVariable DECIMAL
WITH a AS (
    SELECT 
        *
    FROM 
        dbo.mydb
    WHERE 
        condition
),
b AS (
    SELECT 
        * 
    FROM (
        SELECT 
            big switch case   
        FROM 
            dbo.myotherdb
        LEFT JOIN 
            dbo.anotherone
        WHERE 
            condition
),
c AS (
    SELECT 
        a.*
    FROM 
        a
    LEFT JOIN 
        b
)
    SELECT @MyVariable = (
                        CASE
                        WHEN ( 
                            SELECT TOP(1) c.something 
                            FROM c AS c 
                            ) IS NOT NULL THEN 1
                        WHEN (
                            SELECT TOP(1) c.something
                            FROM c AS c 
                            WHERE c.something = 2
                            ) IS NOT NULL THEN 2 - dbo.otherfunction()
                        END)
RETURN @MyVariable
END GO

内容的提问来源于stack exchange，提问作者MyselfAndOnlyMe