SQL:条件 AND 在 where

2021-09-24 00:00:00 sql where sql-server

我正在尝试创建一个允许省略参数的存储过程，但如果提供了参数，则进行 AND 运算:

I'm trying to create a stored procedure that allows parameters to be omitted, but ANDed if they are provided:

CREATE PROCEDURE 
    MyProcedure

    @LastName Varchar(30) = NULL,
    @FirstName Varchar(30) = NULL,
    @SSN INT = NULL
AS

SELECT LastName, FirstName, RIGHT(SSN,4) as SSN
FROM Employees
WHERE 
    (
        (LastName like '%' + ISNULL(@LastName, '') + '%')
            AND 
        (FirstName like '%' + ISNULL(@FirstName, '') + '%')
    )
AND
    (SSN = @SSN)

如果提供了@SSN，我只想做AND.或者有其他方法可以做到这一点?

I only want to do the AND if there's an @SSN provided. Or is there some other way to do this?

如果提供了 SSN，则所有记录都由查询的姓氏/名字部分返回.如果只提供了姓氏/名字，则 AND 将使其成为这样，因此不会返回任何记录，因为 SSN 不会验证.

If an SSN is provided, all records are returned by the LastName/FirstName part of the query. If just a lastname/firstname is provided, the AND makes it so no records are returned since the SSN won't validate.

想法?

补充说明

假设有一个基本记录集:

Assume a basic recordset:

First          Last          SSN  
Mike           Smith         123456789  
Tom            Jones         987654321

如果我们改变上面的程序，使它不包括这个块:

IF we ALTER the Procedure above so it doesn't include this chunk:

AND
    (SSN = @SSN)

然后一切正常，只要我们传递了名字或姓氏.但我希望能够包括 SSN，如果提供的话.

then everything works great, provided we're passed a FirstName or LastName. But I want to be able to include SSN, if one is provided.

如果我将 AND 更改为 OR，那么我会得到一个错误的结果集，因为查询的 FirstName/LastName 部分评估为:

If I change the AND to an OR, then I get a bad result set since the FirstName/LastName portion of the query evalute to:

WHERE (LastName like '%%' and FirstName like '%%')

(当然会返回所有记录)

(which will, of course, return all records)

如果我不能有条件地中止 AND，我希望是这样的:

If I can't conditionally abort the AND, I'm hoping for something like:

AND
    (SSN like ISNULL(@SSN, '%'))

:-)

推荐答案

更改:

AND (SSN = @SSN)

到:

AND (SSN = @SSN or @SSN is null)

如果 SSN 从不为空，你也可以这样做:

If SSN is never null, you could also do:

AND SSN = ISNULL(@SSN, SSN)

相关文章