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 |
|
groston
Yak Posting Veteran
61 Posts |
 Posted - 2006-01-13 : 14:50:25
|
| The challenge is to reply to this post without using the phrase "dynamic SQL"...I would like to write a query that returns data related to a specific record if the record_id parameter is non-zero and returns data for all records if the record_id parameter is zero. (NOTE: No record_id ever has the value 0.)Currently, my stored procedure looks like this:if @record_id = 0 SELECT * FROM myTableelse SELECT * FROM myTable WHERE record_id = @record_idWhile this is correct and functional, it seems somewhat inelegant. What other approach could one use?Thanks. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-01-13 : 14:54:52
|
| Your method is fine in my book but if you're looking for an alternative, here's one:where record_id = isnull(nullif(@record_id,0),record_id)Be One with the OptimizerTG |
 |
|
|
groston
Yak Posting Veteran
61 Posts |
Posted - 2006-01-15 : 17:38:16
|
| Good suggestion - thank you! |
|
|
|
|
|
|
Writing a simple query