如果为空，忽略存储过程中的参数

Question

我正试图通过存储过程在表上获得多输入搜索功能。

我有四个输入参数:一个bool和3个string。当我运行一个字符串和一个bool时，我得到了预期的行。

一旦我尝试将2和3输入(字符串)添加到搜索中，我就没有得到任何数据。如果我填写了所有3个输入(字符串)，它应该返回与这些字符串相关的行。

如果输入为空…然后它应该跳过查询的那一部分

存储过程如下所示:

ALTER PROCEDURE [dbo].[GetUsers]
@Online BIT = NULL,
@UserName NVARCHAR(150) = NULL,
@EmailAddress NVARCHAR(150) = NULL,
@Location NVARCHAR(50) = NULL
AS
SELECT *
FROM Users
WHERE (ISNULL(@Online, 0) = 0 OR (@Online = 1 AND Online = 1)) 
AND (ISNULL(@UserName, UserName) = UserName OR UserName LIKE '%' + @UserName + '%' )
AND (ISNULL(@EmailAddress, EmailAddress) = EmailAddress OR EmailAddress LIKE '%' + @EmailAddress + '%')
AND (ISNULL(@Location, Location) = Location OR Location LIKE '%' + @Location + '%' )
ORDER BY 
Users
FOR XML PATH('Users'), ROOT('Users')
RETURN 0

Answer 1

try this

WHERE  (  (@Online Is Null) or  (@Online = 1 AND Online = 1))
AND ( (@UserName is null)  OR (UserName LIKE '%' + @UserName + '%' ) )
AND (( @EmailAddress is null) or ( EmailAddress LIKE '%' + @EmailAddress + '%'))
AND ( (@Location is null)  or   ( Location LIKE '%' + @Location + '%' ))