Store procedure problem

  • 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

  • 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



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply