/*复制中码用量到预用量(型号内复制)*/
declare @style_no varchar(20)
set @style_no = 'AA70038-18'[@more@]
USE GKSERP_TEST
GO
select top 99 * from style_stru
where style_no='AA70038-18'
--UQTY:中码用量,YQTY:预用量; CountWay:方法 , YcountWay:预用量方法,
/*复制中码用量到预用量(型号内复制)*/
declare @style_no varchar(20)
set @style_no = 'AA70038-18'
update style_stru set YQTY=UQTY,YCOUNTWAY=COUNTWAY,Yulmodifydt=getdate()
where style_no = @style_no
/*说明:复制中码用量到预用量(型号内复制)
格式:pr_CopyDosage 型号编号
建立日期:2009-06-27
Author: Sam
*/
create procedure pr_CopyDosage
( @style_no varchar(20)
)
with encryption
as
begin
begin tran
update style_stru set YQTY=UQTY,YCOUNTWAY=COUNTWAY,Yulmodifydt=getdate()
where style_no = @style_no
if @@error<>0
begin
rollback tran
raiserror('复制失败.',16,-1)
end
else
begin
commit tran
raiserror('复制成功 !',16,-1)
end
end
go
exec pr_CopyDosage 'AA70038-19'
select top 99 * from style_stru
where style_no IN ('AA70038-18','AA70038-19')
ORDER BY SEQ,PART_NO
/*说明:型号间复制预用量。
格式:EXECUTE PR_CopyDosageX 源形体编号,目标形体编号
建立日期:2009-06-27
Author: Sam
*/
create procedure pr_CopyDosageX
( @source_StyleNo varchar(20),
@destination_StyleNo varchar(20)
)
with encryption
as
begin
IF EXISTS( SELECT NAME FROM SYSOBJECTS WHERE NAME='#T' AND TYPE='U')
DROP TABLE #T
select style_no,part_no,Yqty,YCOuntWay into #t from style_stru where style_no=@Source_StyleNo
begin tran
UPDATE D set D.YQTY=S.YQTY,D.YCountWay=S.YCountWay,D.Yulmodifydt=getdate() from style_stru D, #T S
WHERE D.part_no=S.part_no and D.style_no=@destination_StyleNo -- and d.style_NO=s.style_no
if @@error<>0
begin
rollback tran
--raiserror('复制失败.',16,-1)
end
else
begin
commit tran
-- raiserror('复制成功!',16,-1)
end
DROP TABLE #T
end
go
execute pr_CopyDosageX 'AA70038-18','AA70038-19'
-- drop procedure pr_CopyDosageX
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/729024/viewspace-1023544/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/729024/viewspace-1023544/