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 |
|
dave mack
Starting Member
3 Posts |
 Posted - 2004-11-22 : 15:28:05
|
| I have been trying to find a way to return all records if a parameter is null and filter the database results if a parameter is supplied.In Access I could do something likeSelect from ...where name like "*" & OptionalParameterI see ways of passing wildcarded strings, but no way to show all records if the parameter is null.Cheers!Dave |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-11-22 : 15:39:13
|
| select * from ...where name like ISNULL(@OptionalParameter,'') + '%'- Jeff |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-11-23 : 00:39:09
|
quote:
Originally posted by dave mack
Select from ...where name like "*" & OptionalParameter
select from...where name like coalesce('%' + optionalparameter,name)--------------------keeping it simple... |
|
|
|
dave mack
Starting Member
3 Posts |
Posted - 2005-02-22 : 08:20:56
|
| This works great for text. What can I do if the optional parameter is an integer? Say I pass a unique id into the sproc I get that particular record. If I pass nothing, I get all records?Cheers,Dave |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-02-22 : 09:06:05
|
| where (@Param is Null) or (@Param = Column)- Jeff |
|
|
|
|
|
|
parameters and wildcards