| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-09-21 : 22:42:52
|
| Paul writes "is it possible to have an optional where clause and/or use the CASE WHEN...THEN...ELSE...END in conjunction with a WHERE clause in a function or stored procedure?ie. Writing a function that I want to take in 2 parameters: 1st one is mandatory, 2nd is optional (so I give it a default value of Null).Is there a way I can then test (in a select statement) so that if @param2 is not null then WHERE test = @param2 or likeWHERE CASE WHEN @param is not null then test = @param else "whatever" ENDI hope you get what I'm asking. Thanks." |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-09-21 : 23:20:39
|
| WHERE(@param IS NULL OR test = @param)You can get really convoluted here by using parans and imbedding the logic.WHERE(@param IS NULL OR (test = @param AND (@param2 IS NULL OR test1 = @param2)) blah, blah, blah. The bottom line is that you can test. I'm not sure what you are trying to do with the "whatever" though.Does this answer your question?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
paulj
Starting Member
4 Posts |
Posted - 2005-09-22 : 08:20:20
|
| Here's my stored procedurePROCEDURE procGetOrderHistory(@param1 int, @param2 nvarchar(20) = null)ASSELECT ItemNo, Qty FROM tblOrderDetails WHERE OrderNo = @param1 AND IDNo LIKE CASE WHEN @parm2 is null then '%' ELSE @param2 END What I want is to have @param2 to be optional (hence defaulting to null) so then I only want the query to have the "WHERE IDNo= @param2) when @param is NOT NULL, however if I use "IDNo = @param2" as another part of the where clause then it will only bring back where IDNo is null. So basically I'm saying that I want to do "WHERE IDNo= @param2" ONLY when @param2 IS NOT NULL. When @param2 is passed, then I want to add to the WHERE Clause "IDNo = @param2" otherwise I do not want to add that to the WHERE CLAUSE. I also tried using LIKE as I did above, it seems to pretty much work the way I want it to for this situation, but I do not think it will work for all situations, sot is there a better way to accomplish this? I hope this is clearer. thanks. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-09-22 : 23:18:14
|
| I'm either misreading your post, or you're making this too hard.WHERE OrderNo = @param1 AND (@param2 IS NULL OR IdNo = 'blah')MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
teej
Starting Member
9 Posts |
Posted - 2005-09-23 : 06:28:59
|
| This any use to you?Create PROCEDURE procGetOrderHistory(@param1 int, @param2 nvarchar(20) = null)ASdeclare @str varchar(100)select @str = 'Select ItemNo, Qty from tblOrderDetails where ' + case when @param2 is null then 'OrderNo = @param1' else 'IDNo = @param2' endexec(@str) |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-09-25 : 18:23:27
|
| No, that wouldn't be any use to him. He should avoid that like he would leprosy or a serial killer. It's just slightly better than a serial rapist killer or cursor.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
paulj
Starting Member
4 Posts |
Posted - 2005-09-27 : 09:02:38
|
| derrick,thanks. as usual, i was making it harder than it is. 'preciate all the help. |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
|
|
|
Optional WHERE or WHERE CASE WHEN