代码之家 › 专栏 › 技术社区 › MyselfAndOnlyMe

在T-SQL函数内执行多个WITH子句[重复]

with-clause t-sql function sql-server sql

0

MyselfAndOnlyMe · 技术社区 · 2 年前

如何在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子句放在哪里?无论我把它们放在哪里,都会出现语法错误。在我的函数声明之前,在de开始之后的内部,在参数中的select之前。

编辑:我修改了我的示例函数,使其看起来更像我遇到的实际问题。我需要做一个switch案例,而不是select语句。

好的,这就是解决方案:

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

1 回复 | 直到 2 年前

1

2

Thom A 2 年前

你放的 在…内 函数,因为这就是语句的位置。大概 (您没有显示您的尝试)您尝试过 WITH CTE AS (...) SET @Variable... 然而CTE 不能后面跟一个 SET ,它必须是 SELECT 或DML语句。您的CTE中也有语法错误,因为您没有关闭派生表的括号,也没有命名它。

修复这些问题时,它看起来是这样的:

CREATE FUNCTION dbo.MyFunction ()
RETURNS decimal --(p,s) required
AS BEGIN
    DECLARE @MyVariable decimal; --(p,s) required
    WITH
    a AS
        (SELECT *
         FROM dbo.mydb
         WHERE condition = 1),
    b AS
        (SELECT *
         FROM (SELECT bigswitch AS cased
               FROM dbo.myotherdb
                    LEFT JOIN dbo.anotherone ON 1 = 1
               WHERE condition = 1) a 
    ),
    c AS
        (SELECT a.*
         FROM a
              LEFT JOIN b ON 1 = 1)
    SELECT @MyVariable = c.firstdecimal
    FROM c;
    RETURN @MyVariable;
END;
GO

尽管这不太可能是个好主意,但请改用内联表值函数:

CREATE FUNCTION dbo.MyFunction ()
RETURNS table
AS
    RETURN WITH
           a AS
               (SELECT *
                FROM dbo.mydb
                WHERE condition = 1),
           b AS
               (SELECT *
                FROM (SELECT big AS switchcase
                      FROM dbo.myotherdb
                           LEFT JOIN dbo.anotherone ON 1 = 1
                      WHERE condition = 1) a --Alias goes here
           ),
           c AS
               (SELECT a.*
                FROM a
                     LEFT JOIN b ON 1 = 1)
    SELECT c.firstdecimal
    FROM c;
GO