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 |
|
Ciupaz
Posting Yak Master
232 Posts |
 Posted - 2013-08-01 : 03:56:18
|
| Hello all,I have a table with a field that can be null, like this:ID - PlantID - Value1 - Null - 10.52 - 10 - 23,93 - 11 - 4.54 - Null - 4.2...In a stored procedure I have to write a Select statement that has an input @PlantID. SELECT * FROM TableA WHERE PlantID = @PlantIDIf I have @PlantID = -1I have to extract all values.SELECT * FROM TableA How can I write this dynamic WHERE condition? Thanks a lot. Luigi |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-08-01 : 04:05:51
|
SELECT * FROM TableA WHERE PlantID = @PlantID or @PlantID = -1
Too old to Rock'n'Roll too young to die. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-08-01 : 04:38:36
|
| whilst its ok to do it this way in above case with a single parameter, exetnding this approach for too many parameters can cause creation of bad plans for query and might result in a performance issue. In that it might probably be worth using dynamic sql approach as discussed belowhttp://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
Ciupaz
Posting Yak Master
232 Posts |
Posted - 2013-08-01 : 04:49:28
|
| Thanks a lot. It was more simple then I think. Luigi |
|
|
|
|
|
|
Dynamic WHERE condition