Avatar of ZekeLA
Flag for United States of America

asked on 

Designing queryr with optional where clauses for performance

I have to retrieve a list of data from a view based on several different filter options. For example, customer, Date, person, etc.
The end user can choose any or all of these filter options.

Currently, this is done by creating the sql statement from the client web page and passing it to the database. For example, in one case it will be "SELECT * FROM MyView WHERE CustomerID = 24" and in another it will be "SELECT * FROM MyView WHERE Date >= '1/1/2009' AND PersonID = 123".

For several reasons, I want to move this into a stored procedure. If I do, I want to just pass the individual clause values (customerid, date, personid, etc.) to the proc.

I know I can write a where clause like the following to handle this:
   (@CustomerID IS NOT NULL AND CustomerID = @Customer) OR
   (@Date IS NOT NULL AND Date >= @Date) OR
   (@PersonID IS NOT NULL AND PersonID = @PersonID)

Is that the best way or are there performance problems with this?
Microsoft SQL Server

Avatar of undefined
Last Comment

8/22/2022 - Mon