Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-12-06 : 15:42:50
|
Kush writes "Hi Guys,
I have a search form, with 4 fields. All 4 are optional but atleast 1 is compulsory.
One option for the SQL query is to use LIKE keyword
i.e.
select * from Search where ColumnA like '%'+ @Field1 + '%'
and ColumnB like '%'+ @Field2 + '%'
and ColumnC like '%'+ @Field3 + '%'
and ColumnD like '%'+ @Field4 + '%'
I check the cost for this query and it came to 77%
I then made a query with nested IF's that checks which of the 4 fields are empty and omits them from the WHERE CLause.
e.g. if FIELD1,FIELD2,FIELD3 are empty but FIELD4 is not empty. Then the IF Statement for this would be
if select (@Field1)='' and select(@Field2)='' and select(@Field3)='' and select(@Field4)<>''
begin
select * from Search where Column4 like '%' + @Field4 + '%'
end
as u might have guessed, this solution has several IF's
so for my 4 variables, i have 16 IF's like the one on top.
writing these IF's is extremely complicated and tedious
but the cost for this query came out to be 33%.
Is that a substantial performance improvement.??
The prob with the 2nd solution is that as the number of variables keep on increasing, the number of IF's will keep on increasing.... if i have 5 variables, instead of 4, the number of IF's will increase from 16 to 32.
and this SQL SP is not very easy to modify in the future.
What i want to know is, is there a better solution to this.????
if so, please let me know
Kush" |
|
|
|
|
|
Optimising search on multiple fields