April 29, 2010 at 11:04 am
My app have search function to search Customers. Criteria are : Firstname, Lastname and status.
The seach can be done on one criteria, 2 criteria or all 3.
My problem is I don't know how to write a stored proc to support this. If fact, there is 7 possibilities.
Where FirstName = @Firstname
or
Where LastName = @LastName
or
Where status = @status
or
Where FirstName = @Firstname AND Lastname = @LastName
or
Where FirstName = @Firstname AND Status = @status
or
Where LastName = @Lastname AND status = @status
or
Where FirstName = @Firstname AND Lastname = @LastName AND status = @status
Do I have to write 7 different stored proc. Look to me difficult to maintain, imagine if there is 4 criteria instead of 3 I will have to write 21 stored proc for the search fonctionnality?
What's the best approach for this situation?
thank you
Martin
April 29, 2010 at 11:08 am
I had faced this problem earlier. I managed this in my front end. i created dynamic sql and then ran that sql.
April 29, 2010 at 11:32 am
Yup front end is the way to go for these kind of things.
* Noel
April 29, 2010 at 12:08 pm
I've done this before using LIKE and '%'
exec mySP @FirstName, @Lastname, @status
within the stored procedure
set @Firstname = @Firstname + '%'
set @LastName = @LastName + '%'
select * from myTable
Where FirstName LIKE @Firstname
AND Lastname LIKE @LastName
AND status LIKE @status
The front end can send a zero length character string and the sp works.
April 29, 2010 at 3:07 pm
If Dynamic SQL isn't an option, you can also use something like this:
WHERE
(@FirstName IS NULL OR FirstName LIKE @Firstname)
AND (@Lastname IS NULL OR Lastname LIKE @LastName)
AND (@Status IS NULL OR status LIKE @status)
There can be execution plan problems with that approach though.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply