create procedure sys.sp_scriptdelproccore
(
    @artid int                  -- id of the article we are processing
    ,@format tinyint         -- 1 = CALL, 3 = XCALL, 4 = VCALL
    ,@mode tinyint          -- 1 = regular scripting, 2 = snapshot reconciliation scripting, 3 = drop reconciliation procs only
    ,@publishertype tinyint=1        -- 1 = mssqlserver, 2 = heterogeneous
    ,@publisher sysname = NULL		-- is only non-NULL if @publishertype = 2
    ,@usesqlclr bit=0
)
as
begin
    declare @cmd          nvarchar(4000)
        ,@dest_owner   nvarchar(255)
		 ,@dest_tabname sysname
        ,@src_objid    int
        ,@del_cmd      nvarchar(255)
        ,@dest_proc    nvarchar(524)
        ,@this_col     int
        ,@art_col      int
        ,@isset        int
        ,@isvalidcolumn tinyint
        ,@pubid        int
        ,@typestring   nvarchar(255)
        ,@colname sysname
        ,@spacer       nvarchar(10)
        ,@updsub_check bit
        ,@qwhere_string nvarchar(4000)
        ,@commandprefix nvarchar(5)
        ,@repl_freq tinyint
        ,@publication sysname
        ,@userdefinedtypestobasetypes bit
        ,@xmltontext bit
        ,@maxtypestomatchingnonmaxtypes bit
        ,@newdatetimetypestostrings bit
        ,@hierarchyidtovarbinarymax bit
        ,@largeuserdefinedtypestovarbinarymax bit
        ,@spatialtypestovarbinarymax bit
        ,@schema_option bigint
        ,@maptimestamp bit
        ,@article sysname
        ,@unquoted_proc_name sysname

    
    -- constants
    
        ,@callformat tinyint
        ,@xcallformat tinyint
        ,@vcallformat tinyint
        ,@regularmode tinyint
        ,@reconciliationmode tinyint
        ,@droponly tinyint
        ,@pubtypemssqlserver tinyint
        ,@pubtypeheterogeneous tinyint

    
    -- initialize
    
    set nocount on
    select @callformat = 1
            ,@xcallformat = 3
            ,@vcallformat = 4
            ,@regularmode = 1
            ,@reconciliationmode = 2
            ,@droponly = 3
            ,@pubtypemssqlserver = 1
            ,@pubtypeheterogeneous = 2
            ,@maptimestamp = 1
            ,@hierarchyidtovarbinarymax = 0
            ,@largeuserdefinedtypestovarbinarymax = 0
            ,@spatialtypestovarbinarymax = 0
            ,@article = NULL
            ,@userdefinedtypestobasetypes = 0
            ,@xmltontext = 0
            ,@maxtypestomatchingnonmaxtypes = 0
            ,@newdatetimetypestostrings = 0

    
    -- validate @artid
    
    if not exists( select * from sysarticles where artid = @artid AND (type & 1) = 1 )
    begin
        raiserror (14155, 16, 1 )
        return 1
    end
    
    -- validate @format
    
    if (@format not in (@callformat, @xcallformat, @vcallformat))
    begin
        raiserror(21402, 16, 8, '@format')
        return (1)
    end
    
    -- validate @mode
    
    if (@mode not in (@regularmode, @reconciliationmode, @droponly))
    begin
        raiserror(21402, 16, 9, '@mode')
        return (1)
    end
    
    -- validate @publishertype
    
    if (@publishertype not in (@pubtypemssqlserver, @pubtypeheterogeneous))
    begin
        raiserror(21402, 16, 10, '@publishertype')
        return (1)
    end

    
    -- validate @publisher
    
    if (@publisher IS NULL) AND (@publishertype = @pubtypeheterogeneous)
    begin
        raiserror(21425, 16, 7)
        return (1)
    end

    if (@publisher IS NOT NULL) AND (@publishertype = @pubtypemssqlserver)
    begin
        raiserror(21424, 16, 7)
        return (1)
    end

    if (@publishertype = @pubtypemssqlserver)
    begin
		select @publisher = publishingservername()
    end

    
    -- create temp table for scripting
    
    create table #proctext ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
    
    -- get sysarticles information
    
    select @dest_owner = case when dest_owner is NULL then N''
                    else quotename( dest_owner ) + N'.' end
            ,@dest_tabname = dest_table
            ,@src_objid = case when (@publishertype = @pubtypemssqlserver) then objid else artid end
            ,@del_cmd = del_cmd
            ,@pubid = pubid
            ,@schema_option = convert(bigint,schema_option)
	        ,@article = name
    from sysarticles
    where artid = @artid

    if (@schema_option & 0x0000000000000020) = 0x0000000000000020 -- UserDefinedTypesToBaseTypes
    begin
        set @userdefinedtypestobasetypes = 1
    end

    if (@schema_option & 0x0000000010000000) = 0x0000000010000000 -- XmlToNText
    begin
        set @xmltontext = 1
    end

    if (@schema_option & 0x0000000020000000) = 0x0000000020000000 -- MaxTypesToMatchingNonMaxTypes
    begin
        set @maxtypestomatchingnonmaxtypes = 1
    end

    if (@schema_option & 0x0000000000000008) = 0x0000000000000008 -- replicate timestamp as timestamp
    begin
        set @maptimestamp = 0
    end

    if (@schema_option & 0x0000000200000000) = 0x0000000200000000 -- NewDateTimeTypesToStrings
    begin
        set @newdatetimetypestostrings = 1
    end

    if (@schema_option & 0x0000002000000000) = 0x0000002000000000 -- HierarchyIdToVarBinaryMax
    begin
        set @hierarchyidtovarbinarymax = 1
    end

    if (@schema_option & 0x0000001000000000) = 0x0000001000000000 -- LargeUserDefinedTypesToVarBinaryMax
    begin
        set @largeuserdefinedtypestovarbinarymax = 1
    end

    if (@schema_option & 0x0000008000000000) = 0x0000008000000000 -- SpatialTypesToVarBinaryMax
    begin
        set @spatialtypestovarbinarymax = 1
    end

    
    -- get publication info
    
    select @updsub_check = (ISNULL(allow_sync_tran, 0) |ISNULL(allow_queued_tran, 0))
            ,@repl_freq = repl_freq
            ,@publication = name
     from syspublications
    where pubid = @pubid

    
    -- Do not support snapshot publications
    
    if (@repl_freq = 1)
    begin
        raiserror(21515, 16, -1, @publication)
        return 1
    end
    
    -- do we have the right call format
    
    select @commandprefix = case
                    when (@format = @callformat) then N'CALL'
                    when (@format = @xcallformat) then N'XCALL'
                    when (@format = @vcallformat) then N'VCALL'
                    else null end
    if ( 1 != charindex( @commandprefix, upper(@del_cmd collate SQL_Latin1_General_CP1_CS_AS) ) ) or @del_cmd is null
    begin
		raiserror (14156, 16, 1, N'delete', @commandprefix)
		return 1
    end
    
    -- get destination proc name, the format stored inside sysarticles: XXXX [dbo].[xxxxxxxx]
    
    select @dest_proc = case when (@format = @callformat)
            then substring( @del_cmd, 6, len( @del_cmd ) - 4 )
            else substring( @del_cmd, 7, len( @del_cmd ) - 5 ) end

    --in ccs reconcile mode we can no longer use ;2 as proc name, append something to the end to make it *unique*
    select @dest_proc = case @mode when @regularmode then @dest_proc
                                else substring(@dest_proc, 1, len( @dest_proc ) -1 ) + N'_msrepl_ccs]'
                                end
    select @unquoted_proc_name = parsename(@dest_proc, 1)
    
    -- script the dropping and creation of the proc
    
    select @cmd = N'if object_id(N''' + sys.fn_replreplacesinglequote(@dest_proc) collate database_default + N''', ''P'') > 0
                                drop proc ' + @dest_proc
    insert into #proctext(procedure_text) values( @cmd )
    --only keep custom procs in MSreplication_objects for easier cleanup, ccs rec procs are cleaned up by logreader so no need for this
    if (@mode = @regularmode)
    begin
        insert into #proctext(procedure_text) values( N'go' )
        select @cmd = N'if object_id(N''dbo.MSreplication_objects'') is not null
            delete from dbo.MSreplication_objects where object_name = N''' + sys.fn_replreplacesinglequote(@unquoted_proc_name) collate database_default + N''''
        insert into #proctext(procedure_text) values( @cmd )
    end

    --droponly is used by ccs to drop reconciliation proc at the time sync_done token is posted
    
    if (@mode = @droponly)
        goto DROPONLY
    insert into #proctext(procedure_text) values( N'go' )
    insert into #proctext( procedure_text ) values (  N'create procedure ' + @dest_proc + N' ')
    
    -- construct parameter list
    
    select @art_col = 1
            ,@spacer = N' '
            ,@cmd = N' '
    if (@publishertype = @pubtypemssqlserver)
    begin
        declare hccolid cursor local fast_forward for
            select column_id, name from sys.columns where object_id = @src_objid order by column_id asc
    end
    else
    begin
        declare hccolid cursor local fast_forward for
            select colid, name from IHsyscolumns where id = @src_objid order by colorder asc
    end
    open hccolid
    fetch hccolid into @this_col, @colname
    while (@@fetch_status != -1)
    begin
        if (@publishertype = @pubtypemssqlserver)
            select @isvalidcolumn = case when exists (select name from sys.columns where object_id=@src_objid and @this_col=column_id and is_computed != 1) then 1 else 0 end
        else
            select @isvalidcolumn = case when exists (select name from IHsyscolumns where id=@src_objid and @this_col=colid) then 1 else 0 end
        
        -- process each column based on format
        
        if (@format in (@callformat, @vcallformat))
        begin
            
            -- processing CALL/VCALL formats
            -- For VCALL we are looking for PK non computed column
            
            exec @isset = sys.sp_MSiscolpk @src_objid, @this_col, @publishertype
            if @isset = 1
            begin
                
                -- processing PK column
                
                select @typestring = sys.fn_gettypestring (@src_objid, @this_col, @publishertype, @userdefinedtypestobasetypes, @xmltontext, @maxtypestomatchingnonmaxtypes, @maptimestamp, @newdatetimetypestostrings, @hierarchyidtovarbinarymax, @largeuserd
efinedtypestovarbinarymax, @spatialtypestovarbinarymax)
                select @cmd = @cmd + @spacer + N'@pkc' + convert( nvarchar(10), @art_col ) + N' ' + @typestring
                select @art_col = @art_col + 1
                        ,@spacer = N','
            end -- PK column
        end -- CALL/VCALL format
        else
        begin
            
            -- processing XCALL format
            
            -- If this is a computed column, it is only valid if it is part of the primary key
            
            if @isvalidcolumn = 0
                exec @isset = sys.sp_MSiscolpk @src_objid, @this_col, @publishertype
            else
                exec @isset = sys.sp_isarticlecolbitset @this_col, @artid, @publishertype
            if @isset = 1
            begin
                
                -- processing column marked for replication that is not computed
                
                select @typestring = sys.fn_gettypestring (@src_objid, @this_col, @publishertype, @userdefinedtypestobasetypes, @xmltontext, @maxtypestomatchingnonmaxtypes, @maptimestamp, @newdatetimetypestostrings, @hierarchyidtovarbinarymax, @largeuserd
efinedtypestovarbinarymax, @spatialtypestovarbinarymax)
                select @cmd = @cmd + @spacer + N'@c' + convert( nvarchar(10), @art_col ) + N' ' + @typestring
                
                -- processing for updating subscribers:if this is the row version column : need to add to where clause
                
                if (@updsub_check = 1 and @colname = N'msrepl_tran_version')
                    select @qwhere_string = N' and msrepl_tran_version = @c' + convert( nvarchar, @art_col )
                select @art_col = @art_col + 1
                        ,@spacer = N','
            end -- end column replicated and not computed
        end -- XCALL format
        
        -- flush command as necessary
        
        if len( @cmd ) > 3000
        begin
            insert into #proctext(procedure_text) values( @cmd )
            select @cmd = N' '
        end
        fetch hccolid into @this_col, @colname
    end -- while cursor block
    close hccolid
    deallocate hccolid
    insert into #proctext(procedure_text) values( @cmd )
    
    -- VCALL format : add version parameter for the regular scripting mode
    
    if (@format = @vcallformat) and (@mode = @regularmode)
    begin
        insert into #proctext(procedure_text) values( N',@msrepl_tran_version uniqueidentifier ')
    end
    insert into #proctext(procedure_text) values( N'as ')
    insert into #proctext(procedure_text) values( N'begin ')
    
    -- script the delete statetment
    
    insert into #proctext(procedure_text) values( N'delete ' + @dest_owner + quotename(@dest_tabname) )
    
    -- script the where clause
    
    if (@format in (@callformat, @vcallformat))
    begin
        
        -- processing CALL/VCALL formats
        
        exec sys.sp_scriptpkwhereclause @src_objid = @src_objid
                            ,@artid = @artid
                            ,@publishertype = @publishertype
    end
    else
    begin
        
        -- processing XCALL format
        
        exec sys.sp_scriptpkwhereclause @src_objid = @src_objid
                            ,@artid = @artid
                            ,@prefix = N'@c'
                            ,@artcolcounter = 1
                            ,@publishertype = @publishertype
    end
    
    -- additional processing based on mode and format
    
    if (@mode = @regularmode)
    begin
        
        -- regular mode processing
        
        if (@updsub_check = 0)
        begin
            
            -- processing for SQL Server publisher
            
            if (@publishertype = @pubtypemssqlserver)
            begin
                
                -- regular articles : add missing row check
                
                exec sys.sp_MSscript_missing_row_check
            end
        end
        else
        begin
            
            -- updating subscriber article
            
            if (@format = @xcallformat)
                insert into #proctext(procedure_text) values( @qwhere_string )
            else if (@format = @vcallformat)
                insert into #proctext(procedure_text) values( N' and msrepl_tran_version = @msrepl_tran_version ' )
        end
    end -- end regular mode
    
    -- end the scripting
    
    insert into #proctext(procedure_text) values( N'end ')
    
    -- script the inserting of proc entry in MSreplication_objects now that proc is created
    
    --only keep custom procs in MSreplication_objects for easier cleanup, ccs rec procs are cleaned up by logreader so no need for this
    if (@mode = @regularmode)
    begin
        insert into #proctext(procedure_text) values( N'go' )
        select @cmd = N'if columnproperty(object_id(N''dbo.MSreplication_objects''), N''article'', ''AllowsNull'') is not null '
        insert into #proctext(procedure_text) values( @cmd )
        select @cmd = N'insert dbo.MSreplication_objects (object_name, publisher, publisher_db, publication, article, object_type) values (
                                    + N''' + sys.fn_replreplacesinglequote(@unquoted_proc_name) collate database_default + N''' '
                                    + N', N''' + sys.fn_replreplacesinglequote(@publisher) collate database_default + N''' '
                                    + N', N''' + sys.fn_replreplacesinglequote(db_name()) collate database_default + N''' '
                                    + N', N''' + sys.fn_replreplacesinglequote(@publication) collate database_default + N''' '
                                    + N', N''' + sys.fn_replreplacesinglequote(@article) collate database_default + N''' '
                                    + N',''P'')'
        select @cmd = 'exec (''' + sys.fn_replreplacesinglequote(@cmd) + ''')'
        insert into #proctext(procedure_text) values( @cmd )
    end
DROPONLY:
    
    -- flush to client
    
    select procedure_text from #proctext order by c1 asc
    
    -- all done
    
    return 0
end