none
微软为什么要这么写过程呢,有什么好处呢? RRS feed

  • 问题

  • create proc p_GenCascadeDeleteCollect(@alter int = 0)
    as
    begin
        set nocount on
        
        declare @count int
        declare @cascade_type varchar(40)     
        set @cascade_type = 'CascadeDelete'
    
        -- temp table to hold the generated stored proc
        create table #txttbl (id int identity, line varchar(8000), layer int, objecttypecode int)
    
        declare @fn_name as varchar(255)
        set @fn_name = 'fn_CollectFor'+@cascade_type
    
        -- head of the function
        -- if this is at the setup/upgrade phase, then drop and create the function
        if (@alter = 0) 
        begin
            insert into #txttbl (line) values ('if object_id(''' + @fn_name + ''') is not null')
            insert into #txttbl (line) values ('drop function ' + @fn_name)
            insert into #txttbl (line) values ('go')
            insert into #txttbl (line) values ('create function ' + @fn_name)
        end
        else
        -- if this is at the meta-date change phase, then alter the function
            insert into #txttbl (line) values ('alter function ' + @fn_name)
    
        insert into #txttbl (line) values ('(')
        insert into #txttbl (line) values ('    @root_id uniqueidentifier, ')
        insert into #txttbl (line) values ('    @root_otc int,')
        insert into #txttbl (line) values ('    @isoffline int ')
        insert into #txttbl (line) values (')')
        insert into #txttbl (line) values ('returns @tbl table
    ( 	
         oid uniqueidentifier,
         otc int,
         isroot bit default 0,
         processed int default 0
    ) ')
        insert into #txttbl (line) values ('as')
        insert into #txttbl (line) values ('begin')
        insert into #txttbl (line) values ('     declare @null_guid as uniqueidentifier')
        insert into #txttbl (line) values ('     set @null_guid = N''00000000-0000-0000-0000-000000000000''')
    
        create table #entitylayer(objecttypecode int , layer int, constraint _idx_entitylayer_ primary key clustered (objecttypecode, layer))
        -- cascade related stuff
        exec p_TopoSortEntityRelationshipAll @cascade_type, N'not in (0, 2)'
        -- insert the root entity    
        insert into #txttbl (line) values (char(9)+' insert into @tbl values(@root_id, @root_otc, 1, 0)')
    
        declare @line varchar(8000), @line2 varchar(8000)
        -- iterate through each entity ordered by layers
        declare cur_entity cursor fast_forward for
        select objecttypecode, layer from #entitylayer order by layer, objecttypecode
        
        declare @otc int, @layer int
        
        open cur_entity
        fetch next from cur_entity into @otc, @layer
        
        while(@@fetch_status = 0)
        begin
            -- process the restrict link type
            declare @re_pk_name varchar(255), @re_name varchar(255), @re_ra_name varchar(255), 
                    @e_otc int, @re_has_dsc int, @re_repl bit, @re_id uniqueidentifier, @re_ra_id uniqueidentifier
    
            declare cur_rel cursor fast_forward for
                select e.ObjectTypeCode, ra.PhysicalName, pk.PhysicalName, re.PhysicalName, 
                       re.HasDeletionStateCode, re.IsReplicated
                from RelationshipView r, EntityView e, EntityView re, AttributeView pk, AttributeView ra
                where e.EntityId = r.ReferencedEntityId and re.EntityId = r.ReferencingEntityId
                and re.ObjectTypeCode = @otc 
                and pk.EntityId = re.EntityId and pk.IsPKAttribute = 1
                and ra.AttributeId = r.ReferencingAttributeId
                and r.CascadeDelete = 3
            
            open cur_rel
            fetch next from cur_rel into @e_otc, @re_ra_name, @re_pk_name, @re_name, @re_has_dsc, @re_repl 
            declare @first bit
            set @first = 1   
            
            while(@@fetch_status = 0)
            begin
                if(@first = 1) set @line = char(9)+'if(exists(select o.' + @re_pk_name + ' from ' + @re_name + ' o, @tbl c where (('
                else set @line = @line + ' or '
                set @line = @line + '(o.'+@re_ra_name+'=c.oid and c.otc = '+cast(@e_otc as varchar(40)) + ')'
                set @first = 0
                fetch next from cur_rel into @e_otc, @re_ra_name, @re_pk_name, @re_name, @re_has_dsc, @re_repl
            end
            
            if(@first = 0) 
            begin 
                set @line = @line + ')'
                if(@re_has_dsc = 1) set @line = @line + ' and o.DeletionStateCode in (0) '
                set @line = @line + '))) goto rl'
                if(@re_repl = 0) set @line = char(9) + 'if (@isoffline = 0) begin '+@line+' end'
                insert into #txttbl (line) values (@line)
            end
     
            close cur_rel
            deallocate cur_rel
     
            -- process regular cascade link type
            
            -- iterate through all the referencing attribute
            declare cur_ref_attr cursor fast_forward for 
                select distinct re.PhysicalName, ra.PhysicalName, pk.PhysicalName,
                re.EntityId, ra.AttributeId, re.HasDeletionStateCode, re.IsReplicated
                from EntityView re, RelationshipView r, AttributeView ra, AttributeView pk
                where re.EntityId  = r.ReferencingEntityId
                and ra.EntityId = re.EntityId
                and ra.AttributeId = r.ReferencingAttributeId
                and pk.EntityId = re.EntityId
                and pk.IsPKAttribute = 1
                and re.ObjectTypeCode = @otc
                and r.CascadeDelete = 1
    
            open cur_ref_attr
            fetch next from cur_ref_attr into @re_name, @re_ra_name, @re_pk_name, @re_id, @re_ra_id, @re_has_dsc, @re_repl
            
            while(@@fetch_status = 0)
            begin
                declare cur_ref_otc cursor fast_forward for
                    select e.ObjectTypeCode
                    from EntityView e, RelationshipView r
                    where e.EntityId = r.ReferencedEntityId
                    and r.ReferencingEntityId = @re_id
                    and r.ReferencingAttributeId = @re_ra_id
                
                open cur_ref_otc
                fetch next from cur_ref_otc into @e_otc
                set @count = 0
                set @first = 1
                set @line2 = ''
                while(@@fetch_status = 0)
                begin
                    if(@first = 1) set @line2 = '('
                    else set @line2 = @line2 + ', '
                    set @line2 = @line2 + cast(@e_otc as varchar(40))
                    set @first = 0 
                    set @count = @count + 1
                    fetch next from cur_ref_otc into @e_otc
                end
                
                if(@first = 0) 
                begin
                    set @line2 = @line2 + ')'
                    set @line  = char(9) + 'insert into @tbl(oid, otc) select o.'+@re_pk_name+', '+cast(@otc as varchar(40))
                               +' from '+@re_name+' o, @tbl c where c.oid=o.'+@re_ra_name+' and c.otc in '+@line2
                    if(@count < 5) set @line = char(9) + 'if(exists(select top 1 otc from @tbl where otc in '+@line2+')) '+@line
                    if(@re_has_dsc = 1) set @line = @line + ' and o.DeletionStateCode in (0)'
                    if(@re_repl = 0) set @line = char(9) + 'if (@isoffline = 0) begin '+@line+' end'    
                end 
    
                close cur_ref_otc
                deallocate cur_ref_otc
                
                insert into #txttbl (line) values (@line)
    
                fetch next from cur_ref_attr into @re_name, @re_ra_name, @re_pk_name, @re_id, @re_ra_id, @re_has_dsc, @re_repl
            end
            close cur_ref_attr
            deallocate cur_ref_attr    
            
            fetch next from cur_entity into @otc, @layer
        end
    
        close cur_entity
        deallocate cur_entity
    
        -- tail of the function
        insert into #txttbl (line) values ('    return')
        insert into #txttbl (line) values ('rl: insert into @tbl values(@null_guid, 0, 0, 0) return')
        insert into #txttbl (line) values ('end')
    
        select line from #txttbl where line is not null order by id, objecttypecode
      
        -- drop the temp table
        drop table #entitylayer
        drop table #txttbl 
    end
    

    学习,学习,再学习
    2009年11月20日 8:08

答案

  • 建议不要用时间来研究这个, 这个使用在 Cascade Relationship 上的。但升级或安装 Rollup 的时候可能会改变的。 你也不必要修改这个 Stored Procedure, 微软不支持的。 
    Darren Liu | 刘嘉鸿 | MS CRM MVP | English Blog: http://msdynamicscrm-e.blogspot.com | Chinese Blog: http://liudarren.spaces.live.com
    2009年11月24日 14:26
    版主

全部回复

  • 很少人会去研究这个东西 呵呵


    Batistuta Cai-刀客 | 蔡敏生 | MS CRM MVP | Blog:http://caims.cnblogs.com
    2009年11月24日 12:56
    版主
  • 建议不要用时间来研究这个, 这个使用在 Cascade Relationship 上的。但升级或安装 Rollup 的时候可能会改变的。 你也不必要修改这个 Stored Procedure, 微软不支持的。 
    Darren Liu | 刘嘉鸿 | MS CRM MVP | English Blog: http://msdynamicscrm-e.blogspot.com | Chinese Blog: http://liudarren.spaces.live.com
    2009年11月24日 14:26
    版主
  • 建议不要用时间来研究这个, 这个使用在 Cascade Relationship 上的。但升级或安装 Rollup 的时候可能会改变的。 你也不必要修改这个 Stored Procedure, 微软不支持的。 
    Darren Liu | 刘嘉鸿 | MS CRM MVP | English Blog: http://msdynamicscrm-e.blogspot.com | Chinese Blog: http://liudarren.spaces.live.com

    只是感觉这样写效率不高。
    学习,学习,再学习
    2009年11月25日 2:45
  • 我都不喜使用 Cursor 的。
    Darren Liu | 刘嘉鸿 | MS CRM MVP | English Blog: http://msdynamicscrm-e.blogspot.com | Chinese Blog: http://liudarren.spaces.live.com
    2009年11月25日 5:09
    版主
  • 我都不喜使用 Cursor 的。
    Darren Liu | 刘嘉鸿 | MS CRM MVP | English Blog: http://msdynamicscrm-e.blogspot.com | Chinese Blog: http://liudarren.spaces.live.com

    :)
    学习,学习,再学习
    2009年11月25日 12:27