SQL: Conditional AND in where

Question

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)

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

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.

Ideas?

---

Additional Clarification

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)

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.

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)

---

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

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

:-)

Answer 1

Change:

AND (SSN = @SSN) 

to:

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

If SSN is never null, you could also do:

AND SSN = ISNULL(@SSN, SSN)