USP_BATCHCOMMITPARAMETERS_LOAD

CREATE procedure dbo.USP_BATCHCOMMITPARAMETERS_LOAD
(@ID uniqueidentifier)
as
begin
    set nocount on;

    with DUPLICATERECORDDETECTIONSEARCHLIST_CTE as
    (
        select
            BATCHTEMPLATE.ID as BATCHTEMPLATEID,
            SPECXML.value('declare namespace bbspec="bb_appfx_batchtype";
                data(/bbspec:BatchTypeSpec/bbspec:DuplicateRecordCheck/@SearchListID)[1]
            ', 'uniqueidentifier') DUPLICATERECORDDETECTIONSEARCHLISTID,
            case when SPECXML.exist('declare namespace bbspec="bb_appfx_batchtype";
                /bbspec:BatchTypeSpec[(@RequireExceptionBatch cast as xs:boolean?) eq xs:boolean("true")]
            ') = 1 then 1 else 0 end as REQUIREEXCEPTIONBATCH
        from dbo.BATCHTEMPLATE
        left join dbo.BATCHTYPECATALOG on BATCHTYPECATALOG.ID = BATCHTEMPLATE.BATCHTYPECATALOGID
    )
    select
        BATCH.CREATEOUTPUTIDSET,
        BATCH.OUTPUTIDSETNAME,
        BATCH.OVERWRITEOUTPUTIDSET,
        BATCH.PURGEBATCH,
        BATCH.CREATEEXCEPTIONBATCH,
        BATCH.EXCEPTIONBATCHNAME,
        BATCH.CREATECONTROLREPORT,
        BATCH.VALIDATEBATCH,
        BATCH.CHECKFORDUPLICATERECORDS,
        -- Casting ID to a string to preserve backwards compatibility since the ID was originally 

        -- returned typed as an XML fragment which would then be read as a string in code

        case 
            when SEARCHLISTSETTINGS.REPLACEMENTSEARCHLISTID is not null then cast(SEARCHLISTSETTINGS.REPLACEMENTSEARCHLISTID as nvarchar(36))
            else cast(DUPLICATERECORDDETECTIONSEARCHLIST_CTE.DUPLICATERECORDDETECTIONSEARCHLISTID as nvarchar(36))
        end DUPLICATERECORDDETECTIONSEARCHLISTID,
        coalesce(DUPLICATERECORDDETECTIONSEARCHLIST_CTE.REQUIREEXCEPTIONBATCH, 0) as REQUIREEXCEPTIONBATCH
    from dbo.BATCH
    left join DUPLICATERECORDDETECTIONSEARCHLIST_CTE on BATCH.BATCHTEMPLATEID = DUPLICATERECORDDETECTIONSEARCHLIST_CTE.BATCHTEMPLATEID
    left join dbo.SEARCHLISTSETTINGS on SEARCHLISTSETTINGS.ID = DUPLICATERECORDDETECTIONSEARCHLIST_CTE.DUPLICATERECORDDETECTIONSEARCHLISTID
    where BATCH.ID = @ID;
end