Last post Feb 07, 2009 08:12 PM by blackberrycurve
Feb 07, 2009 07:22 PM|blackberrycurve|LINK
I have a table like
Id FName LName Department Email Phone
1 fa la dep1 e@.com 94877798
and there is a web form where we can search for a person. We can search based on single field or multiple fields.
FName and LName,
FName and Department and Email....................................
ie search should be only on the input columns but not on others.
I tried to query by two different ways cwhich donot yeild the desired result
1. Select FName, LName from Table1
where FName = @FName and LName = @LName and Department = @Department and Email = @Email
will give result only if all the input fields are filled.
2. Select FName, LName from Table1
where FName = @FName or LName = @LName or Department = @Department or Email = @Email
will give result to any combination but at the same time if some one searches by" fname and lname" it will search by "fname or lname"
One solution I know is to write different stored procedures for different combinations then I have to write hundreds of stored procedures since my table has many columns and search is on many fields.
Please help me...
Feb 07, 2009 07:39 PM|donkiely|LINK
There are two classic solutions to this problem, both using stored procedures, although you could implement the code in your client application if you really wanted to not use stored procs.
One is to test for each combination of parameters and execute a stored procedure based on that. Something like this, although you can simplify the overall code:
IF @FName NOT IS NULL AND @LName NOT IS NULL AND @Department IS NULL AND @Email IS NULL
SELECT FName, LName FROM Table1 WHERE FName = @FName AND LName = @LName
And have various other IF blocks for other combinations. This works because you have relatively few parameters. It could get messy quickly as you add more.
The other way is with dynamic SQL, where you build the select statement as a string, based on the non-null parameters. This makes for cleaner code, but uses--ick--dynamic SQL.
What version of SQL Server are you using? If 2008, some of the T-SQL enhancements may make this easier.
Feb 07, 2009 07:45 PM|gmmastros|LINK
Select FName, LName
where (Coalesce(@FName, '') = '' or FName = @FName)
And (Coalesce(@LName, '') = '' or LName = @LName)
And (Coalesce(@Department, '') = '' or Department = @Department)
And (Coalesce(@Email, '') = '' or Email = @Email)
If this works for you, and you would like for me to explain, just let me know and I will.
Feb 07, 2009 07:57 PM|izharulislam|LINK
You need to use like operator in your query.
i am using northwind database.
select * from customers
where contactname like 'Ana%' and contactTitle like '%'
Feb 07, 2009 08:12 PM|blackberrycurve|LINK
Its working. Thank you very much