how to get record and update its upper level in sql thru procedure

Question

3.50/5 (2 votes)

See more:

hello Everyone,
i have a problem in my stored procedure

SQL

insert into tbl_level(Parentid,childid,rewardpoint,earnedmoney,noofchild) values(@parentkey,@CusId,1,convert(decimal,(@BookingAmt/10)),1)
if exists (select 1 from tbl_level where childid=@parentkey)
begin
update tbl_level set earnedmoney= earnedmoney+@earnedmoney where parentid=(select Parentid from tbl_level where Childid=@parentkey)
     end

table has parentid and childid and earnedmoney columns now there is functionality when any record get inserted it's parent get commission , i want its parent's parents get commission upto 4 level.
e.g A has a child B and B has child C and C has child D so i want when D become parent of Child E commission amount should be go to D,C,B,A respectively.

Posted 13-Dec-13 19:40pm

Mohd Arif Khan

Updated 13-Dec-13 20:10pm

v3

Add a Solution

Comments

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 14-Dec-13 2:06am

Then select one by one as you have did and update. Where is the problem?

Mohd Arif Khan 14-Dec-13 2:07am

sir upto one level its fine but how to get select its upper level and update it

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 14-Dec-13 2:30am

Try my answer below.

Maciej Los 15-Dec-13 17:08pm

Please, provide more details about table structure and sample data...

Mohd Arif Khan 16-Dec-13 0:29am

tbl_level
parentid childid earnedmoney
cus_1 cus_2 5000
cus_2 cus_3 5000
cus_3 cus_4 5000

i want when cus_4 becomes parent of node cus_5 the parent of cus_3 get commissiom and cus_2 and this gone upto 4 level how to perform it.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Hammad · Answer 1 · 2013-12-13T21:52:00

Tadit Dash Solution Is Good But What Happened When You Have Excessive Number Of Parents/Childs.
One Solution Is To Use While Loop With exists keyword instead of using if
like

SQL

Declare @nextParent int;
 
insert into tbl_level(Parentid,childid,rewardpoint,earnedmoney,noofchild) values(@parentkey,@CusId,1,convert(decimal,(@BookingAmt/10)),1)
while exists (select @nextParent=childid from tbl_level where childid=@parentkey)
begin
         update tbl_level set earnedmoney= earnedmoney+@earnedmoney where parentid=(select Parentid from tbl_level where Childid=@parentkey)
end

An@nd Rajan10 · Answer 2 · 2013-12-13T23:04:00

Solution 3

try this

C#

if exists (select 1 from tbl_level where childid=@parentkey)
begin
insert into tbl_level(Parentid,childid,rewardpoint,earnedmoney,noofchild) values(@parentkey,@CusId,1,convert(decimal,(@BookingAmt/10)),1)
update tbl_level set earnedmoney= earnedmoney+@earnedmoney where parentid=(select Parentid from tbl_level where Childid=@parentkey)
     end

Posted 13-Dec-13 23:04pm

An@nd Rajan10

Comments

Mohd Arif Khan 14-Dec-13 5:37am

@Joy Please mention what you have updated in existing code. and insert statement is needed to be executed one time only.

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Answer 3 · 2013-12-13T20:29:00

Solution 1

Try something like below.

SQL

Declare @nextParent int;

insert into tbl_level(Parentid,childid,rewardpoint,earnedmoney,noofchild) values(@parentkey,@CusId,1,convert(decimal,(@BookingAmt/10)),1)
if exists (select 1 from tbl_level where childid=@parentkey)
begin
         select @nextParent=childid from tbl_level where childid=@parentkey
 
         update tbl_level set earnedmoney= earnedmoney+@earnedmoney where parentid=(select Parentid from tbl_level where Childid=@parentkey)

         if exists (select 1 from tbl_level where childid=@nextParent)
         begin
                 select @nextParent=childid from tbl_level where childid=@nextParent

                  update tbl_level set earnedmoney= earnedmoney+@earnedmoney where parentid=(select Parentid from tbl_level where Childid=@nextParent)
         end
         
         -- Continue this flow...
end

Posted 13-Dec-13 20:29pm

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Updated 13-Dec-13 22:37pm

v2

Comments

Mohd Arif Khan 14-Dec-13 3:45am

it's getting error Incorrect syntax near '='. on select @nextParent=childid from tbl_level where childid=@parentkey line, and childid column is varchar type and you have matched it with in select statement how it will matched ever

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 14-Dec-13 4:37am

I have updated the answer. Try now.

Mohd Arif Khan 16-Dec-13 5:06am

hello Tadit, i'hv applied it but it's just updating upto one level

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 16-Dec-13 5:10am

As I said "continue the flow"... That means do the same thing for next levels.
Do you understand now?

Mohd Arif Khan 16-Dec-13 5:51am

i understand and did the same brother but it's not going to update the upper level.

Mohd Arif Khan 16-Dec-13 5:53am

declare @nextparent varchar(20)
declare @money decimal(18,0)
if(@bookingamt<=25000)
set @money=1000
else
set @money=2000

insert into tbl_level(Parentid,childid,rewardpoint,earnedmoney,noofchild) values(@parentkey,@CusId,1,convert(decimal,(@BookingAmt/10)),1)
if exists (select 1 from tbl_level where childid=@parentkey)
begin

select @nextParent=parentid from tbl_level where childid=@parentkey

update tbl_level set earnedmoney+=@money where parentid=(select Parentid from tbl_level where Childid=@parentkey)

if exists (select 1 from tbl_level where childid=@nextParent)
begin
select @nextParent=childid from tbl_level where childid=@nextParent

update tbl_level set earnedmoney= earnedmoney+@money where parentid=(select Parentid from tbl_level where Childid=@nextParent)
end
if exists (select 1 from tbl_level where childid=@nextParent)
begin
select @nextParent=childid from tbl_level where childid=@nextParent

update tbl_level set earnedmoney= earnedmoney+@money where parentid=(select Parentid from tbl_level where Childid=@nextParent)
end
if exists (select 1 from tbl_level where childid=@nextParent)
begin
select @nextParent=childid from tbl_level where childid=@nextParent

update tbl_level set earnedmoney= earnedmoney+@money where parentid=(select Parentid from tbl_level where Childid=@nextParent)
end
if exists (select 1 from tbl_level where childid=@nextParent)
begin
select @nextParent=childid from tbl_level where childid=@nextParent

update tbl_level set earnedmoney= earnedmoney+@money where parentid=(select Parentid from tbl_level where Childid=@nextParent)
end
end

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 16-Dec-13 8:49am

Because you have done wrong. I am updating my answer. wait.

The below is wrong and the

select @nextParent=~~parentid~~ from tbl_level where childid=@parentkey

It should be "childid". And the flow is wrong. I am updating. Wait for sometime.

Mohd Arif Khan 16-Dec-13 5:56am

have a look to the flow but it could not update the 3rd level and one more thing on 2nd level it's updating the value by awkward way

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 16-Dec-13 8:52am

No all seems correct. Try once by correcting the line I asked you in my above comment.
Let me know if it works or not. If it won't work, then I will check again.

Mohd Arif Khan 17-Dec-13 0:29am

hii,Tadit
after doing this the same problem its happening like A->B->C->D->E respectively these are child in alphabetical order when E registered the money earned by D is suppose 5000. and and C gets 2000 so the new amount for C is 7000 at this level it's ok, when B's amount just become 13000 to 21000, m not not how it's value get so much. and after this no level value is modifying.

how to get record and update its upper level in sql thru procedure

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us