SQL Server存储的pProcedure是null和equals运算符

I need a stored procedure with 2 parameters, @TId is for top Id and @CId for CultureId which is selecting proper culture items.

我需要一个带有2个参数的存储过程，@ TId用于顶部Id，而@CId用于CultureId，它选择适当的文化项目。

I want my where clause looks like this when @TId parameter is null:

当@TId参数为null时，我希望我的where子句看起来像这样：

WHERE TopId IS NULL AND CultureId = @CId

but when I specify an actual int value for @TId, it should look like this:

但是当我为@TId指定一个实际的int值时，它应该如下所示：

WHERE TopId = @TId AND CultureId = @CId

I have an issue with Where clause shown by comment near it.

我对附近的评论显示的Where子句有疑问。

This statement is not working with error:

此语句不适用于错误：

Incorrect syntax near is

附近的语法不正确

Code:

码：

ALTER PROCEDURE hierarchicalcategoryview
    @TId INT = NULL,
    @CId INT = 1    
AS
BEGIN
    SET NOCOUNT ON;

    ;WITH HierarchicalCategoryTable (TopId, Id, Name, Level) AS
    (
        SELECT 
            c.TopId, c.Id, c.Name, 0 AS Level
        FROM 
            categoryview AS c
        --the where clause which fails
        WHERE 
            CASE 
               WHEN @TId IS NULL 
                  THEN (TopId IS NULL AND CultureId = @CId)
               WHEN @TId IS NOT NULL
                  THEN (TopId = @TId and CultureId = @CId)
            END

        UNION ALL

        SELECT 
            c.TopId, c.Id, c.Name, Level + 1
        FROM 
            categoryview AS c    
        INNER JOIN 
            HierarchicalCategoryTable AS d ON c.TopId = d.Id 
        WHERE
            c.CultureId = @CId
    )
    SELECT *
    FROM HierarchicalCategoryTable 
    ORDER BY Level ASC, Id ASC
END

Summary I can't dynamically change

总结我无法动态改变

WHERE TopId IS NULL AND etc...

and

和

WHERE TopId = @TId AND etc...

2 个解决方案

#1

Try

尝试

WHERE  ISNULL(TopId, -1) = ISNULL(@TId, -1) and CultureId = @CId

assuming -1 is not a value actually stored in TopId.

假设-1不是实际存储在TopId中的值。

PS: Your WHERE CASE clause fails because THEN returns a boolean expression which is not valid in TSQL.

PS：您的WHERE CASE子句失败，因为THEN返回一个在TSQL中无效的布尔表达式。

Rewriting as

重写为

WHERE (CultureId = @CId)
AND ((@TId is NULL AND TopId is null)
    OR (TopId = @TId))

may help, but probably the OR causes terrible performance

可能会有所帮助，但可能OR会导致糟糕的表现

#2

Here's how you could fix the CASE in your WHERE clause, in case you were wondering:

以下是如何在WHERE子句中修复CASE，以防您想知道：

ALTER procedure hierarchicalcategoryview
    @TId int = null,
    @CId int = 1    

as
begin
    set NOCOUNT on;



;WITH HierarchicalCategoryTable (TopId, Id, Name, Level)
AS
(
    SELECT c.TopId, c.Id, c.Name,  
        0 AS Level
    FROM categoryview AS c
    --the where clause which fails
    WHERE CultureId = @CId
        AND ISNULL(TopId,-1) = CASE
                                WHEN @TId is NULL THEN -1
                                WHEN @TId IS NOT null THEN @TId
                                END


    UNION ALL
    SELECT c.TopId, c.Id, c.Name,
        Level + 1
    FROM categoryview AS c    
    INNER JOIN HierarchicalCategoryTable AS d
        ON c.TopId = d.Id 
                where c.CultureId = @CId
)
SELECT *
FROM HierarchicalCategoryTable order by Level asc,Id asc
end

#1

Try

尝试

WHERE  ISNULL(TopId, -1) = ISNULL(@TId, -1) and CultureId = @CId