use optional parameter in WHERE clause

问题:

I have a SP

ALTER PROCEDURE dbo.sp_Compare
    @lst varchar(100),
    @frst varchar(100) = NULL,
    @passportNo varchar(50) = NULL
AS
SELECT * FROM dbo.User
WHERE LastName like '%' + @lst + '%'
    AND (
         FirstName like @frst + ' %'
         OR FirstName like '% ' + @frst + ' %'
         OR FirstName like '% ' + @frst
         OR FirstName = @frst
        )
    OR Passport = @passportNo;

Sometimes @frs or @passportNo or both the details may not be available

so I want to change the above query such that when optional parameter is not passed(ie., when its value is null) that parameter shouldn't be considered for filtering on that particular column

viz., if @frst is NULL then result shouldn't be filtered based in FirstName, it should work as if there was no FirstName like '%' + @frst + '%' condition in WHERE clause..

How can I write the query without writing the same logic repeatedly for different cases?

回答1:

WHERE (@frst IS NULL OR 
    (
         LastName like '%' + @lst + '%'
         AND (
             FirstName like @frst + ' %'
             OR FirstName like '% ' + @frst + ' %'
             OR FirstName like '% ' + @frst
             OR FirstName = @frst
        )
    )
 )
 AND (@passportNo IS NULL OR
        Passport = @passportNo);

Edit: Actually this should be more efficient:

WHERE ( @frst IS NULL OR FirstName like '%' + @frst + '%' )
AND   ( @lst  IS NULL OR LastName  like '%' + @lst  + '%' )
AND   ( @passportNo IS NULL OR Passport = @passportNo )