June 12, 2009 at 2:07 am
Hiiiiiiiii
I have a problem like below:-
i have a table like below
Id Name
1 David
2 Paul
Null Dave
i am getting values of this table through a sp which takes @Id as a input.
Now my problem is that i want to retrieve rows not containing NULL in ID field when input parameter @Id is not null, otherwise all entries will be allowed
it should be something like below
where id =(Case when @Id is not null then else.... end)
plzz Help me....
June 12, 2009 at 3:18 am
Hi,
Instead of using CASE within the where clause, you can use CASE first and then put where clause.
As,
syntax
CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END
SELECT ProductNumber, Category =
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
END,
Name
FROM
ORDER BY ProductNumber;
GO
I guess this will be more clear then what you have written. sorry if iam wrongly interpreted.
Thanks,
Raj
June 12, 2009 at 7:03 am
Hiiiiiiiii
I have a problem like below:-
i have a table like below
Id Name
1 David
2 Paul
Null Dave
i am getting values of this table through a sp which takes @Id as a input.
Now my problem is that i want to retrieve rows not containing NULL in ID field when input parameter @Id is not null, otherwise all entries will be allowed
it should be something like below
where id =(Case when @Id is not null then else.... end)
plzz Help me....
You could use the following, though I'm not sure I follow you completely. This returns the record with the ID passed. If @ID is null, it gives you everything:
declare @id tinyint
select id, name
from test
where (CASE WHEN @ID is not null THEN
CASE WHEN @ID = ID then 1 else 0 END
ELSE 1 END)=1
Randy
June 12, 2009 at 8:26 am
Thanks a lot simsr it worked perfectly for me...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply