Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to use Member table, Member_Account table and Class table to calculate Monthly Fee of any member, i've created a procedure and nested cursors for this purpose, please help if anyone understands my problem

create procedure CalcFee(@Member_Id int)

as

Declare
@Class_Price decimal(18,2),
@Total decimal(18,2),
@Class_Id int;

Begin
Set @Class_Id=(Select Class_Id from Member_Account where Member_Id=@Member_Id);

Set @Class_Price=(Select Class_Price from Class where Class_Id=@Class_Id);

Set @Total= @Total + @Class_Price;

update Member set Monthly_Fee=@Total where Member_Id=@Member_Id;

end;



Declare firstcursor Cursor For

Select Member_Id from Member

declare

@Member_1 int;

Begin

Open firstcursor;

Fetch next from firstcursor Member_Id into @Member_1;

while @@FETCH_STATUS=0

Begin

Declare secondcursor Cursor For

Select Member_Id from Member_Account

declare

@Member_2 int;

Begin

Open secondcursor;

Fetch next from firstcursor Member_Id into @Member_2;

while select * from Member_Account where Member_Id=@Member_2

Begin

exec CalcFee @Member_Id = @Member_2;

Fetch next from firstcursor Member_Id into @Member_2;

end;

close secondcursor;

deallocate secondcursor;

end

Fetch next from firstcursor Member_Id into @Member_1;

end;

close firstcursor;

deallocate firstcursor;

end;
share|improve this question
7  
Better to just show us the table structures, example data and desired results so we don't have to reverse engineer your requirement. –  Martin Smith Apr 6 '13 at 19:23

1 Answer 1

You don't need cursors for this at all. Whenever you think that you need to loop or treat members one row at a time, you need to step back and think about why you need to do that. SQL Server operates best on set-based logic, not row-by-row iteration. Here is a set-based update statement that updates all members at once, doesn't require any loops (never mind nested cursors), and I think does what you want (you didn't initialize @Total so currently that is always going to be NULL - perhaps you meant to initialize it as 0 or perhaps that comes from somewhere else that your code currently doesn't illustrate).

UPDATE m
 SET m.Monthly_Fee = c.Class_Price
 FROM dbo.Member AS m
 INNER JOIN dbo.Member_Account AS ma
 ON m.Member_Id = ma.Member_Id
 INNER JOIN dbo.Class AS c
 ON ma.Class_Id = c.Class_Id;

Of course, I wonder why you need to update the member table at all. Don't you send out the bills once a month? Can't you calculate this at runtime without even needing a Monthly_Fee column (perhaps storing the fees charged in a history table)? Why constantly update the member table with information you can get elsewhere?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.