我有以下条件。
如果一个值传递给@userid参数,我需要过滤这些 用户记录。
如果没有传递的值,那么我需要带上所有记录。
列收件人不是null 。
但是,即使我传递了价值,在查询下方仍会返回所有记录。
DECLARE @UserId INT
SET @UserId = 47
SELECT *
FROM Message MR
WHERE MR.CompanyId = 1
AND (
MR.RecipientId IS NOT NULL
OR MR.RecipientId = @UserId
)
只需在变量上使用布尔表达式
declare @UserId int = null
SELECT *
FROM Message MR
WHERE MR.CompanyId = 1 and (MR.RecipientID = @UserId or @UserId IS NULL)
我相信您可以使用更简单的表单,因为您提供ID
DECLARE @UserId INT
SET @UserId = 47
SELECT *
FROM Message MR
WHERE MR.CompanyId = 1
AND MR.RecipientId = @UserId
如果没有提供ID
SELECT *
FROM Message MR
WHERE MR.CompanyId = 1
AND (
@UserId IS NULL
OR MR.RecipientId = @UserId -- OR get this one, redundant
)
为什么:
SELECT *
FROM Message MR
WHERE MR.CompanyId = 1
AND (
MR.RecipientId IS NOT NULL -- get All NOT NULL *(basically all)
OR MR.RecipientId = @UserId -- OR get this one, redundant
)
还有另一种选择,可能不是有效的,看看:
DECLARE @UserId INT
SET @UserId = 47
SELECT *
FROM Message MR
WHERE MR.CompanyId = 1
AND MR.RecipientId = (
CASE
WHEN @UserId IS NULL
THEN MR.RecipientId
ELSE @UserId
END
)