带参数和游标的存储过程

USE [Database]
GO
/****** Object:  StoredProcedure [dbo].[MDMTools_SequenceContainsMoreThanOneEement_Dispose_Material]    Script Date: 2016/6/28 10:01:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MDMTools_SequenceContainsMoreThanOneEement_Dispose_Material]
 @MaterialCode Nvarchar(50)
as
BEGIN 
    declare @Materialid int;
    declare @Unitid int;
    declare @Message Nvarchar(max);
    set xact_abort on

    begin tran 
    set @Message=''
    declare _cur cursor for
    select  id  from Materialheaders where code=@MaterialCode and isactive=1
    open _cur
    fetch next from  _cur into @Materialid
    while @@fetch_status=0
        begin 
            begin try
                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)
                
                update MaterialMeasuringUnits set AlternativeUoMId=1 where id=@Unitid
                
                if (@Unitid is null)
                    begin
                        select @Message=N'@Unitid为空'
                    end
                else
                    begin
                        select @Message=N'执行成功：物料代码： '+@MaterialCode+N' 物料测量单位表ID： '+convert (nvarchar,@Unitid)
                    end

                print @Message
            end try 
            begin catch
                select @Message= ERROR_MESSAGE() 
                print @Message
                rollback tran
                close _cur 
                deallocate _cur
                return
            end catch

            fetch next from  _cur into @Materialid
        end

            close _cur 
           deallocate _cur

    commit tran
END