处理空参数的存储过程 [英] Stored procedure to handle null parameter
问题描述
我确定这有一个非常简单的答案,我找不到……我在数据库中有一个简单的层次结构,其中每一行都有一个 ParentId.如果 ParentId IS NULL
,那么它是一个根元素.我有存储过程:
I'm sure this has a very simple answer I am not finding... I have a simple hierarchy in a database where each row has a ParentId. If ParentId IS NULL
, then it's a root element. I have the stored procedure:
CREATE PROCEDURE GetByParent @parentId int
AS BEGIN SELECT * FROM TABLE1 WHERE ParentId = @parentId END
如果我发送一个整数它工作正常,但如果我发送 NULL
它变成 ParentId = NULL
,这在 ANSI 中不起作用.我知道有 COALESCE(@parentId, ParentId)
,但是当 @parentId 为 NULL
时它会返回所有行.我可以执行 IF ELSE 语句并复制查询(一个带有 = @parentId
,另一个带有 IS NULL
),但我确信有更好的方法.
It works fine if I send an integer, but if I send NULL
it becomes ParentId = NULL
, which doesn't work in ANSI. I know there is COALESCE(@parentId, ParentId)
, but that returns all rows when @parentId IS NULL
. I can do an IF ELSE statement and copy the query (one with = @parentId
and the other with IS NULL
), but I'm sure there's a better way.
推荐答案
在单独的条件下处理 NULL 情况:
Handle the NULL case in a separate condition:
SELECT *
FROM TABLE1
WHERE ParentId = @parentId
OR (ParentId IS NULL AND @parentId IS NULL)
这篇关于处理空参数的存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!