December 5, 2014 at 11:23 am
I want the user to be able to select various customers based upon if the first name, last name, or middle names are selected. The idea is for the user to select one, two, and/or three of the parameters and get the results they want. The following sql works but I am wondering if there is another way to write the sql below and get the same results where the code is more specific?
SELECT [lastName]
,[firstName]
,[middleName]
,[suffix]
,a.[userid]
from [TEST].[dbo].[Identity]
where ([lastName] like '%' + @lname + '%' and
[firstName] like'%' + @fname + '%' and
[middleName] like'%' + @mname + '%')
or ([lastName] like '%' + @lname + '%' and
[firstName] like'%' + @fname + '%' and
@mname is null)
or ([lastName] like '%' + @lname + '%' and
[middleName] like'%' + @mname + '%' and
@fname is null)
or ([firstName] like '%' + @fname + '%' and
[middleName] like'%' + @mname + '%' and
@lname is null)
or ([lastName] like '%' + @lname + '%' and
@fname is null and
@mname is null)
or (@lname is null and
[firstName] like'%' + @fname + '%' and
@mname is null)
or (@lname is null and
@fname is null and
[middleName] like'%' + @mname + '%')
or (@fname is null and @lname is null and @mname is null)
order by [lastName], [firstName], [middleName]
I am asking this question since the query does not work in a .net web application. I thought if the code could be rewritten, then maybe the sql would work for the .net application
December 5, 2014 at 11:40 am
Take a look at Gail Shaw's article on catch-all queries.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
If you have any questions, come back and we'll help you.
December 5, 2014 at 2:06 pm
That query pattern doesn't work (well) in SQL Server either!! 🙂 DEFINITELY need to refactor it!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply