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 |
|
Jomypgeorge
Starting Member
31 Posts |
 Posted - 2010-08-13 : 01:44:59
|
| Hi all My current work includes a few stored procedures that fetch rows according to given conditions. Say i have a table with student_id, name, age, markthen in my sp there are four parameters. But its not necessary to have data in all parameters. some may be null. i need the best way to search on table with optional search conditions.first i did it by creating a string according to parameters that are not empty, and executing it. now im using a query like(table.name = @strName or @strName is null) and(table.age= @intAge or @intAge is null) let me know the proper way to accomplish this..Thanks in advance |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-13 : 03:04:39
|
| table.name = @strName or @strName is null) and(table.age= @intAge or @intAge is null) is fine.If you are using dynamic SQL you can just include in the WHERE clause the tests for parameters that are NOT NULL |
 |
|
|
Jomypgeorge
Starting Member
31 Posts |
Posted - 2010-08-13 : 05:31:59
|
| Thanks Kristen |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-08-13 : 10:16:43
|
| Have a look at this blogpost from Gail too. Will be useful.http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ |
|
|
|
|
|
|
Fetching data with optional parameters