Issue with SQL Server select statement

I need help with a select statement that I’m currently struggling with. The scenario is this:

select * from mytable WHERE isActive=1

1. and if only id is provided, select everything where the id=@id
2. if firstname and lastname is provided, then select everything where firstname=@firstname and lastname=@lastname regardless of the ID
3. if either firstname or lastname is provided, select everything where firstname=@firstname or lastname=@lastname regardless of the ID again
4. if id, firstname and lastname provided, just select where firstname=@firstname and lastname=@lastname regardless of the ID again

Here’s my query:

DECLARE @ID INT =25,
@firstname NVARCHAR(100),
@lastname NVARCHAR(100);
SELECT * from mytable
where isActive=1
and ( ID = -1 or ID = @ID) 
or (firstname = @firstname)
or (lastname = @lastname) 
or (firstname = @firstname and lastname = @lastname)

somehow I’m not getting the results expected, for example when I provide both firstname and lastname, it shows everyone with the firstname regardless of their lastname 🙁

Please help

Answer

Not sure if you need a union, especially UNION ALL, but you have two choices. First one is pretty, it works with small data sets. The second option looks ugly, but gives you almost guaranteed best performance.

SELECT * FROM mytable WHERE isActive=1
    AND (
        (@ID IS NOT NULL AND @firstname IS NULL AND @lastname IS NULL AND @ID = ID) OR
        (@firstname IS NOT NULL AND @lastname IS NOT NULL AND firstname=@firstname and lastname=@lastname) OR
        (@firstname IS NOT NULL AND @lastname IS NULL AND firstname=@firstname) OR
        (@firstname IS NULL AND @lastname IS NOT NULL AND lastname=@lastname)
    )

---

IF @ID IS NULL AND @firstname IS NULL AND @lastname IS NULL
SELECT * FROM mytable WHERE isActive=1;

IF @ID IS NOT NULL AND @firstname IS NULL AND @lastname IS NULL
SELECT * FROM mytable WHERE isActive=1 AND @ID = ID;

IF @firstname IS NOT NULL AND @lastname IS NOT NULL 
SELECT * FROM mytable WHERE isActive=1 AND firstname=@firstname AND lastname=@lastname;

IF @firstname IS NOT NULL AND @lastname IS NULL 
SELECT * FROM mytable WHERE isActive=1 AND firstname=@firstname;

IF @firstname IS NULL AND @lastname IS NOT NULL 
SELECT * FROM mytable WHERE isActive=1 AND lastname=@lastname;

That is with no difference between #2 & #4, unless you can describe it.