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 - 2004-05-19 : 17:19:43
|
| writes "I have created a search where the where clause is built dynamically based on user input into search fields ( nothing special yet ). I decided that I didnt want to use dynamic sql to build the where clause. So I tried to find an alternative method.After thinking about it , I wondered if I could use coalesce to handle the conditional logic. The Coalesce would handle the case when the field is null and shouldnt be used in the search. So, why not use the field itself as the default non null coalesce argument.So..select searchKey1,searchKey2....,searchKeyN from MyTable t where t.searchField1 = COALESCE(@searchKey1,t.searchKey1)and t.searchField2 = COALESCE(@searchKey2,t.searchKey2)..and t.searchFieldN = COALESCE(@searchKeyN,t.searchKeyN)etcIt worked, but I am wondering if there are any specific issues that I should be aware of using this method. I assume I can still use indexes on the search fields for speeding up the searching" |
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2004-05-19 : 17:19:43
|
| I'd check to be sure your query is using the indexes for those columns. In most cases that forces a table scan. |
 |
|
|
|
|
|
COALESCE in a WHERE clause