問題描述

我正在嘗試創建一個允許省略參數的存儲過程，但如果提供了參數，則進行 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)

這篇關于SQL:條件 AND 在 where的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！