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 |
|
magz
Starting Member
3 Posts |
 Posted - 2009-02-27 : 12:46:09
|
| Hi..I need to write a search stored procedure in that around 12 fields(like name,dob,city,etc) are there and every field is optional only and a partial search only..to write this stored procedures i need to write the 12 fields in the where condition with or clause (or) Is there any kind of method is available for this...reply me ASAP...Thank u.. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2009-02-27 : 23:05:24
|
magz, the question is not very clear to me, so I am guessing here.This is what I understand: Your stored procedure may pass in 12 parameters - name, dob city, etc. The caller may choose to not provide some or all of the parameters - for example, they may provide only the city, and everything else would be passed in as null. In that case, you want to get all records that have the matching city regardless of the name or date of birth or other criteria.You can accomplish this by where conditions such as these:where coalesce(@name,name) = name and coalesce(@city,city) = city etc... orwhere (@name = name or @name is null) and (@city = city or @city is null) etc... You may want to refine it further - for example, :instead of a null, if the caller passed in empty string you would want to handle that correctly using nullif(@name,''):if the caller passed in a string with spaces or trailing/leading spaces you would want to handle that correctly using ltrim(rtrim(@name)):if you want to do partial matching, you would change the where condition to name like '%'+@name+'%'and so on.Again, the question is not really clear to me, so if this is not what you are looking for, post a little bit more detail with some examples etc. |
 |
|
|
|
|
|
|
|
Search Stored Procedure