May 23, 2010 at 12:00 pm
hello Expert
My problem is If/Else condition not work in store procedure
i create store procedure i just test If /Else condition it not work
if ClientAmount is greater then 500 it insert TotalAmount Add ClientAmount+10 and insert TotalAmount show 510 And if ClientAmount less then 500 it insert TotalAmount show 490 less show
i create table
Create table Profiles(Names varchar(25),ClientAmount int,TotalAmountint)
go
create proc Pro
as
if exists (select * from profiles where ClientAmount >= 500)
begin
update profiles
set Totalamount=ClientAmount+10
where Totalamount is Null
end
else
if exists(select * from profiles where ClientAmount <=500)
begin
update profiles
set totalamount=Client-10
where Totalamount is Null
end
May 23, 2010 at 1:55 pm
The problem is you're updating all rows with your first statement, since there is no WHERE condition that would limit the number of rows affected.
Another option to get the result you want is to get rid of the EXIST subqueries and do it all in one path (untested):
UPDATE profiles
SET Totalamount= CASE WHEN ClientAmount >= 500 THEN ClientAmount + 10
ELSE Client -10 END
FROM profiles
WHERE Totalamount is Null
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy