1 USE [Database]
2 GO
3 /****** Object: StoredProcedure [dbo].[MDMTools_SequenceContainsMoreThanOneEement_Dispose_Material] Script Date: 2016/6/28 10:01:42 ******/
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8 ALTER PROCEDURE [dbo].[MDMTools_SequenceContainsMoreThanOneEement_Dispose_Material]
9 @MaterialCode Nvarchar(50)
10 as
11 BEGIN
12 declare @Materialid int;
13 declare @Unitid int;
14 declare @Message Nvarchar(max);
15 set xact_abort on
16
17 begin tran
18 set @Message=''
19 declare _cur cursor for
20 select id from Materialheaders where code=@MaterialCode and isactive=1
21 open _cur
22 fetch next from _cur into @Materialid
23 while @@fetch_status=0
24 begin
25 begin try
26 select @Unitid=id from MaterialMeasuringUnits where AlternativeUoMId = ( select AlternativeUoMId from MaterialMeasuringUnits where materialheaderid=@Materialid group by AlternativeUoMId having count(AlternativeUoMId)>1 ) and materialheaderid=@Materialid and (IsEAN =0 or IsEAN is null)
27
28 update MaterialMeasuringUnits set AlternativeUoMId=1 where id=@Unitid
29
30 if (@Unitid is null)
31 begin
32 select @Message=N'@Unitid为空'
33 end
34 else
35 begin
36 select @Message=N'执行成功：物料代码： '+@MaterialCode+N' 物料测量单位表ID： '+convert (nvarchar,@Unitid)
37 end
38
39 print @Message
40 end try
41 begin catch
42 select @Message= ERROR_MESSAGE()
43 print @Message
44 rollback tran
45 close _cur
46 deallocate _cur
47 return
48 end catch
49
50 fetch next from _cur into @Materialid
51 end
52
53 close _cur
54 deallocate _cur
55
56 commit tran
57 END
