Issue with SQL Server select statement

Question

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

select * from mytable WHERE isActive=1

and if only id is provided, select everything where the id=@id
if firstname and lastname is provided, then select everything where firstname=@firstname and lastname=@lastname regardless of the ID
if either firstname or lastname is provided, select everything where firstname=@firstname or lastname=@lastname regardless of the ID again
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

Is this mysql or sql server, because you tagged both? Am I right in this logic: #2 is Use ID if that is the only param. #4 is Ignore ID when other params are provided — Charlieface
– Charlieface, Commented Jan 5, 2021 at 2:24
@Charlieface The ID should be ignored if either firstname or lastname is provided, otherwise it searches with the ID. At least one of the three variable is required. — Helen Neely
– Helen Neely, Commented Jan 5, 2021 at 2:27

Slava Murygin · Accepted Answer · 2021-01-05 02:07:55Z

1

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.

answered Jan 5, 2021 at 2:07

Slava Murygin

1,9551 gold badge11 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Helen Neely Over a year ago

Thanks for this massive help, the first code seems to be doing what I was looking for. Not sure how it's going to perform with 10s of thousands of records - who cares :-)

Charlieface Over a year ago

#2 is Use ID if that is the only param. #4 is Ignore ID when other params are provided

Slava Murygin Over a year ago

@HelenNeely Agree. With small number of records the first one is perfect.

Charlieface · Accepted Answer · 2021-01-05 01:49:52Z

It is possible to write these as one query, but owing to the way indexes are used, it will probably result in an inefficient table scan. Therefore you are best off using UNION ALL to join the separate queries:

;WITH cte AS (
    select * from mytable WHERE isActive=1
)
SELECT *
    FROM cte
    WHERE firstname = @firstname AND @lastname IS NULL
UNION ALL
SELECT *
    FROM cte
    WHERE lastname = @lastname AND @firstname IS NULL
UNION ALL
SELECT *
    FROM cte
    WHERE firstname = @firstname AND lastname = @lastname
UNION ALL
SELECT *
    FROM cte
    WHERE ID = @ID
        AND @firstname IS NULL AND AND @lastname IS NULL
OPTION(OPTIMIZE FOR UNKNOWN);

The UNKNOWN bit hints to the compiler that you don't know what may be entered, and it shouldn't keep the parameter values from the first run (parameter sniffing).

An alternative hint is OPTION(RECOMPILE), which means the query will be recompiled on every run. This has overhead, though.

RSax · Accepted Answer · 2021-01-05 02:01:48Z

0

Write it as a series of IF statements:

--Test #1
DECLARE @ID INT = 25, @firstname NVARCHAR(100) = 'Florian', @lastname NVARCHAR(100) = 'Voss';

IF (@firstname IS NOT NULL AND @lastname IS NOT NULL)
    SELECT *
    FROM [AdventureWorks2019].[Person].[Person]
    WHERE (firstname = @firstname AND lastname = @lastname)

IF @firstname IS NOT NULL AND @lastname IS NULL
    SELECT *
    FROM [AdventureWorks2019].[Person].[Person]
    WHERE (firstname = @firstname)
GO

--Test #2
DECLARE @ID INT = 25, @firstname NVARCHAR(100) = 'Florian', @lastname NVARCHAR(100) = NULL;

IF (@firstname IS NOT NULL AND @lastname IS NOT NULL)
    SELECT *
    FROM [AdventureWorks2019].[Person].[Person]
    WHERE (firstname = @firstname AND lastname = @lastname)

IF (@firstname IS NOT NULL AND @lastname IS NULL)
    SELECT *
    FROM [AdventureWorks2019].[Person].[Person]
    WHERE (firstname = @firstname)
GO

answered Jan 5, 2021 at 2:01

RSax

3383 silver badges15 bronze badges

2 Comments

Slava Murygin Over a year ago

I like that. It allows to use specific plans for each select query and you do need to use option recompile or OPTIMIZE FOR UNKNOWN

Charlieface Over a year ago

@SlavaMurygin You do need to use OPTIMIZE FOR UNKNOWN unless you re-save the parameters into local variables

Yitzhak Khabinsky · Accepted Answer · 2021-01-05 02:27:02Z

0

Please try the following method. Though the WHERE clause predicates are not SARGable.

SQL

DECLARE @ID INT =25
    , @firstname NVARCHAR(100) = NULL
    , @lastname NVARCHAR(100) = NULL;

SELECT * 
FROM mytable
WHERE isActive = 1
    AND ID = COALESCE(@ID, -1) -- or COALESCE(@ID, ID) depending on the logic
    AND firstname = COALESCE(@firstname, firstname)
    AND lastname = COALESCE(@lastname, lastname);

edited Jan 5, 2021 at 2:27

answered Jan 5, 2021 at 1:48

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

7 Comments

Charlieface Over a year ago

OPTION RECOMPILE will make them sargeable?

Yitzhak Khabinsky Over a year ago

@Charlieface, IMHO, no. sqlshack.com/…

Charlieface Over a year ago

Huh? If for example: @ID=null, @firstname='John',@lastname='Smith', then the predicate compiles down to ( null = -1 OR ID = null) AND firstname = COALESCE('John', firstname) AND lastname = COALESCE('Smith', lastname), after eliding this becomes: firstname='John' and lastname='Smith'

Charlieface Over a year ago

Incidentally, your query does not deal with ID as OP requested. I think it should be and ( (ID = @ID AND @firstname IS NULL AND @lastname IS NULL) OR (firstname...)

Yitzhak Khabinsky Over a year ago

@Charlieface, I updated the answer. The same technique is applied to the ID column.

|

Collectives™ on Stack Overflow

Issue with SQL Server select statement

4 Answers 4

3 Comments

Comments

2 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

2 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related