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

https://stackoverflow.com/questions/65572058/issue-with-sql-server-select-statement January 05, 2021 at 09:31AM