Delete Cursor (Cont.) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Delete Cursor (Cont.)

Author

Topic

MGA
Starting Member

28 Posts

Posted - 2009-05-17 : 01:58:49

How can i delete cursor (not by allocate)
The database i develpoed is for a pharmaceutical company so that i have a table for the products and another table for the list of bonuses for eaech product so that the amount that the client demand must by compared with all bonuses for that product.
example:
if i had a product A the bonuses list for it are
amount bonus
10 1
20 3
30 6
so that if the client demands 23 packet of A 3 packets must added automatically
So the purpose of this cursor is move through that table (bonuses).
(Sorry for My bad language)
and thanks.
The proc:
create proc quantitybonus(@ppid int,@qin int,@qout int output)
as
begin
declare @bonusammount int;
declare @bonus int;
declare bonuscursor2 cursor for (select bonusamount , bonus from DrugsBonus where productid = @ppid)
open bonuscursor2
fetch bonuscursor2 into @bonusammount,@bonus
if(@bonusammount > @qin)
begin
set @qout = 0;
return 0;
end if
while (@@sqlstatus =0)
begin
exit when
if (@qin > @bonusammount) then
fetch bonuscursor2 into @bonusammount , @bonus
else
;
end if;

end
close bonuscursor2
deallocate bonuscursor2
end
go

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-17 : 03:09:52

think set based. You dont need cursor for this. i think as per your description, what you're looking for is this


create proc quantitybonus(@ppid int,@qin int,@qout int output)
as
begin 
select p.*,p.qin + coalesce(db.bonus,0) as qtyincludingbonus
from product p
outer apply (select top 1  bonus
             from DrugsBonus 
             where productid=p.productid
             and bonusamount<=p.qin
             order by bonusamount desc)db
where p.productid = @ppid--your required product id
go

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources