proc_ord_qty_add.sql

工厂采购系统,DELPHI+SQL SERVER,三层技术

if object_id('dbo.Proc_Ord_Qty_Add') is not null
drop procedure dbo.Proc_Ord_Qty_Add
go

Create Procedure Proc_Ord_Qty_Add
@pono varchar(30) = null,
@Qty  numeric(18,2) = 0.0,
@itemseq int	= null
as
declare
  @old_qty numeric(18,2) 

  select @old_qty = isnull(quantity,0) from ord_item
	where pono =@pono and itemseq =@itemseq and iscom ='Y'

  if(@Qty >@old_qty)
 begin
	update ord_item
		set iscom ='N'
	where pono =@pono and itemseq =@itemseq
 end

 if exists(select  pono from ord_mst where pono =@pono
 and completed ='YES' or status = 'F')
 begin
	update ord_mst 
		set completed ='NO',
		    status = 'O'
	where pono =@pono
 end