May 9, 2016 at 5:01 pm
I have a an optional parameter stored procedure that is currently being used on an employee search screen inside of a web application. I'm trying to add an additional optional parameter that allows for filtering certain values from the same table column. I'm trying to allow the user to filter by Employee type A, B, or A and B. Can anyone offer any assistance? I'm having trouble with the logic that would allow the user to filter by both A and B values from the same table column.
CREATE TABLE Employee
(
EmpID int,
LastName varchar(50),
FirstName varchar(50),
Address varchar(255),
EmpType varchar(1)
);
EmpType table sample
A
B
C
B
C
A
B
CREATE PROCEDURE EmpSearch
@FirstName varchar(50) = null,
@LastName varchar(50) = null,
@EmpType1 varchar(1) = null,
@EmpType2 varchar(1) = null
AS
BEGIN
DECLARE @EmpTypeVal1 varchar(1)
DECLARE @EmpTypeVal2 varchar(2)
If EmpType1 = '1' Set @EmpTypeVal1 = 'A'
If EmpType2 = '1' Set @EmpTypeVal2 = 'B'
SELECT ID, FirstName, LastName, EmpType
FROM tblEmp
WHERE
(@FirstName IS NULL OR (FirstName = @FirstName))
(@LastName IS NULL OR (LastName = @LastName))
AND (@EmpType1 IS NULL OR (EmpType = @EmpType1 ))
AND (@EmpType2 IS NULL OR (EmpType = @EmpType2 ))
' AND (@EmpType2 IS NULL OR (EmpType = @EmpType2 ) OR (@EmpType1 IS NULL OR (EmpType = @EmpType1 ) )
END
May 9, 2016 at 5:08 pm
The dreaded "IS NULL OR" construct. DON'T DO THIS AS WRITTEN!!
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 13, 2016 at 2:03 am
Another option would be to turn the query into dynamic SQL and append
'WHERE searchValue in (''' + @inParameterString +''')'
The @inParameterString is them passed in as "A", "B" or "A','B"
Which will result in the Where clause looking like
'WHERE searchValue in ('A')'
'WHERE searchValue in ('B')'
'WHERE searchValue in ('A','B')'
However you will never be able to build a query plan that is high performance in all cases as it will be subject to parameter sniffing. Also if you are taking the parameter list from a non-secure source (e.g. Web Form) you will need to protect against SQL injection attacks.
I am not saying that you SHOULD do this, in fact I would reccomend against it if you can, but it is a possiblity
May 13, 2016 at 6:26 am
aaron.reese (5/13/2016)
Another option would be to turn the query into dynamic SQL and append'WHERE searchValue in (''' + @inParameterString +''')'
The @inParameterString is them passed in as "A", "B" or "A','B"
Which will result in the Where clause looking like
'WHERE searchValue in ('A')'
'WHERE searchValue in ('B')'
'WHERE searchValue in ('A','B')'
However you will never be able to build a query plan that is high performance in all cases as it will be subject to parameter sniffing. Also if you are taking the parameter list from a non-secure source (e.g. Web Form) you will need to protect against SQL injection attacks.
I am not saying that you SHOULD do this, in fact I would reccomend against it if you can, but it is a possiblity
Actually dynamic SQL is in many cases/ways THE BEST way to solve this classic "open-ended search" problem. It is especially useful in situations where you can eliminate joins to a table that you aren't even touching for filtering. And you are giving the optimizer EXACTLY the right stuff to get the best query plan.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply