“if”附近有语法错误

内部if语句位置错误。with格式为

With <col> as 
(
  ---
)
SELECT ---

此外,我不认为努利夫在这里有目的。一般来说,如果不使用函数就可以编写,则不要使用函数(由于性能考虑)

最终程序为:

CREATE PROCEDURE sp
    @id int,
    @filter varchar(255) = NULL
AS
BEGIN
    IF (@id != 0)
    BEGIN
        IF (@filter = '')
        BEGIN
            WITH cte AS
            (
                --SELECT here...
            )
            SELECT *
            FROM cte;
        END
    END
END

CTE需要在“if(null if(@filter,”)为空)”中。CTE必须在创建后立即使用。这应该有效:

CREATE PROCEDURE sp
    @id int,
    @filter varchar(255) = NULL
AS
BEGIN
    IF (NULLIF(@id, 0) IS NOT NULL)
    BEGIN
        IF (NULLIF(@filter, '') IS NULL)
        BEGIN
            WITH cte AS
            (
                --SELECT here...
            )
            SELECT *
            FROM cte;
        END
    END
END