只有使用游标来循环。
Declare Cur_Tmp Cursor Local Scroll
for
Select * From Table Order by 定货数量 Desc然后用Fetch Next一个个循环
Open Cur_Tmp
Fetch Next From Cur_Tmp
While @@Fetch_Status = 0
Begin
....
End...
和在前台一样的。
Declare Cur_Tmp Cursor Local Scroll
for
Select * From Table Order by 定货数量 Desc然后用Fetch Next一个个循环
Open Cur_Tmp
Fetch Next From Cur_Tmp
While @@Fetch_Status = 0
Begin
....
End...
和在前台一样的。
-------------------------------
定单表需增加一个自增的ID作为主键。
create procedure Sendout
(
@pro_id varchar(50),
@amt int
)
as
begin tran
declare @id int,@ordamt int
Declare cur_tmp Cursor for
Select id,ordamt-outamt as ordamt from yourtable
where pro_id=@pro_id and ordamt-outamt>0
order by price Desc
open cur_tmp
fetch next from cur_tmp into @id,@ordamt
while @amt>0 and @@fetch_status=0
begin
if @amt>@ordamt
begin
update yourtable set outamt=outamt+@ordamt where id=@id
set @amt=@amt-@ordamt
fetch next from cur_tmp into @id,@ordamt
end
else
begin
update yourtable set outamt=outamt+@amt where id=@id
set @amt=0
end
end
commit tran
if @error!=0
begin
rollback tran
return(-1000)
end
return(0)
-----------------------------------------------------
执行:
exec Sendout '001',300
不好意思,忘了释放游标了:
---------------------------------------------------
定单表需增加一个自增的ID作为主键。
create procedure Sendout
(
@pro_id varchar(50),
@amt int
)
as
begin tran
declare @id int,@ordamt int
Declare cur_tmp Cursor for
Select id,ordamt-outamt as ordamt from yourtable
where pro_id=@pro_id and ordamt-outamt>0
order by price Desc
open cur_tmp
fetch next from cur_tmp into @id,@ordamt
while @amt>0 and @@fetch_status=0
begin
if @amt>@ordamt
begin
update yourtable set outamt=outamt+@ordamt where id=@id
set @amt=@amt-@ordamt
fetch next from cur_tmp into @id,@ordamt
end
else
begin
update yourtable set outamt=outamt+@amt where id=@id
set @amt=0
end
end
close cur_tmp deallocate cur_tmp --这里
commit tran
if @error!=0
begin
rollback tran
return(-1000)
end
return(0)
假定表名为TT,测试通过,但是前提是“编号”+“定价”可唯一确定一条记录,否则无效。
declare @num int,@no char(3)
set @num = 300
set @no = '001'update tt
set 出货数量 = case when c.ns + c.定货数量 < @num then tt.定货数量 else @num - c.ns end
from
( select c1,定货数量,单价,(select isnull(sum(定货数量),0) from tt b where b.编号 = @no and b.定货数量 > tt.定货数量) ns
from tt where 编号 = @no
) c
where tt.c1 = @no and c.ns < @num and tt.编号 = c.编号 and tt.单价 = c.单价
假定表名为TT,测试通过,但是前提是“编号”+“定价”可唯一确定一条记录,否则无效。
declare @num int,@no char(3)
set @num = 300
set @no = '001'update tt
set 出货数量 = case when c.ns + c.定货数量 < @num then tt.定货数量 else @num - c.ns end
from
( select c1,定货数量,单价,(select isnull(sum(定货数量),0) from tt b where b.编号 = @no and b.定货数量 > tt.定货数量) ns
from tt where 编号 = @no
) c
where tt.c1 = @no and c.ns < @num and tt.编号 = c.编号 and tt.单价 = c.单价
declare @num int,@no char(3)
set @num = 300
set @no = '001'update tt
set 出货数量 = case when c.ns + c.定货数量 < @num then tt.定货数量 else @num - c.ns end
from
( select 编号,定货数量,单价,(select isnull(sum(定货数量),0) from tt b where b.编号 = @no and b.单价 > tt.单价) ns
from tt where 编号 = @no
) c
where c.编号 = @no and c.ns < @num and tt.编号 = c.编号 and tt.单价 = c.单价
高真是高,服你。