April 25, 2002 at 12:18 pm
Hi, in my sp, I need to do the following logic:
while ( @mv_i <= @mv_count )
begin
select @mv_dummy = item
from mv_table
-- then base on the @mv_dummy's value
do something
-- for example:
when @mv_dummy = 1
update table_1
when @mv_dummy = 2
update table_2
when @mv_dummy = 3
call another_sp
I am thinking if I can use a case statement like this:
case item
when 1
then update table_1
when 2
then update table_2
when 3
then call_another_sp
else
do somthing else
end
end
But when I compile the code, it always complaints syntax error. If I simply return a value in case statement, then it is O.K.
I am wondering if I can do some complicated logic inside case statement (like update or call other function), as what we can do in C language.
I haven't seen elsif statement in sql server.
Looks like I can only do:
if ...
begin
end
else
begin
if
begin
end
else
begin
end
.....
Correct me if I have the wrong impression, since I am new to sql server.
All I want to do is, either use a case statement or if, elsif statement in sql server. Any recommendation is appreciated.
Abby
April 25, 2002 at 12:32 pm
update mytable
set myvalue =
case
when mv_dummy = 1
then "a"
when mv_dummy = 2
then "b"
else "c"
end
where Mytable.MyPK = @x
Steve Jones
April 26, 2002 at 4:59 pm
Hi, Thanks for the reply. What I really would like to know is
when @mv_dummy = 1,
update table_1
when @mv_dummy = 2
update table_2
when @mv_dummy = 3
do_something_else_like_calling_a_sp,
Table_1 is not the same as table_2.
Can I do that?
Thanks.
Abby
April 27, 2002 at 6:36 am
No, then you're really trying to do something a bit different. You can do it by building the sql and passing it to Exec(). In other words, the case will help you build the string, but not be included in the string.
Andy
April 27, 2002 at 8:14 am
well you can use else if you carry out your validations
if condition1
BEGIN
-- do something
END
else if condition2
BEGIN
-- do something 2
END
else if condition3
BEGIN
-- do something 3
END
Hope this solves your problem.
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India
April 27, 2002 at 3:43 pm
Hi, Thanks. The 'else if' clause resolves my problem. I though SQL Server doesn't have the equivalence for elsif in oracle, since
in my example book, it only has 'if ... else... if... else', and I was an oracle db programmer and trying to port some code to sql server.
Great Thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply