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 |
|
floydtagle
Starting Member
2 Posts |
 Posted - 2009-03-19 : 21:25:35
|
| Hi,Im new in SQL, i just want to know if there is a way to create a query that search with multiple parameters but if one of the parameter is blank or empty it will not use that parameter.for example i have 3 parameter, a1 a2 and a3, if a3 is blank the query will not use the parameter or the query will not search the table using that parameter.something like this: select * from tbl where a1 = isnotempty(@a1) and a2 = isnotempty(@a2) and a3 = isnotempty(@a3) please someone help me..thanks |
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-03-20 : 00:19:11
|
| select * from tbl where (@a1 is null or a1= @a1) and (@a2 is null or a2= @a2) and (@a3 is null or a3= @a3) |
 |
|
|
floydtagle
Starting Member
2 Posts |
Posted - 2009-03-20 : 02:54:25
|
| this query will do both, what i need is to validate if parameter is not empty before the query use that parameter. thanks for your reply |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-20 : 09:27:58
|
quote:
Originally posted by floydtagle
this query will do both, what i need is to validate if parameter is not empty before the query use that parameter. thanks for your reply
select * from tbl where a1 = coalesce(@a1,a1) and a2 = coalesce(@a2,a2) and a3 = coalesce(@a3,a3) this is way to do in sql. if you want to validate then validation part needs to be done at your front end application |
|
|
|
|
|
|
Search parameters and Validate if Empty