USP_IDSET_CREATEIDSETTABLE_FROMXML

create procedure dbo.USP_IDSET_CREATEIDSETTABLE_FROMXML
(
    @CURRENTAPPUSERID uniqueidentifier,
    @NAME nvarchar(300),
    @DESCRIPTION nvarchar(1024),
    @COLUMNTYPENAME nvarchar(100),
    @RECORDTYPEID uniqueidentifier,
    @USEINQUERYDESIGNER bit,
    @CHANGEAGENTID uniqueidentifier,
    @IDSET xml,
    @IDSET_TABLENAME nvarchar(100) output,
    @IDSET_FUNCTIONNAME nvarchar(105) output,
    @IDSET_FUNCTIONNAME_IDEXISTS nvarchar(115) output   
)
as
begin
    declare @IDSET_ID uniqueidentifier
    declare @IDSET_ADHOCQUERYID uniqueidentifier
    declare @IDSET_SMARTQUERYINSTANCEID uniqueidentifier

    select
        @IDSET_ID = IDSETREGISTER.ID,
        @IDSET_ADHOCQUERYID = IDSETREGISTERADHOCQUERY.ADHOCQUERYID,
        @IDSET_SMARTQUERYINSTANCEID = IDSETREGISTERSMARTQUERYINSTANCE.SMARTQUERYINSTANCEID,
        @IDSET_TABLENAME = IDSETREGISTER.DBOBJECTNAME
    from dbo.IDSETREGISTER
        left join dbo.IDSETREGISTERSMARTQUERYINSTANCE on IDSETREGISTER.ID = IDSETREGISTERSMARTQUERYINSTANCE.IDSETREGISTERID
        left join dbo.IDSETREGISTERADHOCQUERY on IDSETREGISTER.ID = IDSETREGISTERADHOCQUERY.IDSETREGISTERID
    where IDSETREGISTER.NAME = @NAME

    declare @ISNEW bit = 1
    if @IDSET_ID is null
    begin
        set @IDSET_TABLENAME  = 'IDSETGENERIC_' + replace(newid(),'-','_');
    end
    else
    begin
        set @ISNEW = 0

        if @IDSET_ADHOCQUERYID is not null and @IDSET_ADHOCQUERYID <> '00000000-0000-0000-0000-000000000000'
        begin
            raiserror('BBERR_SELECTIONCANNOTBECREATED_ADHOCQUERY_ASSOCIATION', 13, 1);
            return
        end

        if @IDSET_SMARTQUERYINSTANCEID is not null and @IDSET_SMARTQUERYINSTANCEID <> '00000000-0000-0000-0000-000000000000'
        begin
            raiserror('BBERR_SELECTIONCANNOTBECREATED_SMARTQUERY_ASSOCIATION', 13, 1);
            return
        end

        exec dbo.USP_IDSETREGISTER_GETAPPLOCK @IDSET_ID, 0
    end

    set @IDSET_FUNCTIONNAME = 'UFN_' + @IDSET_TABLENAME;
    set @IDSET_FUNCTIONNAME_IDEXISTS = @IDSET_FUNCTIONNAME + '_IDEXISTS';

    -- Create the static table which will hold the static ID set

    exec dbo.USP_IDSET_CREATESTATICTABLE @IDSET_TABLENAME, @COLUMNTYPENAME

    -- Create the functions associated with the ID set

    exec dbo.USP_IDSET_CREATEORUPDATE
        @IDSET_FUNCTIONNAME,
        @IDSET_TABLENAME,
        1,
        @COLUMNTYPENAME,
        '',
        '',
        null,
        @CHANGEAGENTID

    -- Create the record in the ID set register

    exec dbo.USP_IDSETREGISTER_CREATEORUPDATE
        @IDSET_ID output,
        @NAME,
        @DESCRIPTION,
        @IDSET_TABLENAME,
        0,
        @RECORDTYPEID,
        @USEINQUERYDESIGNER,
        1,
        null,
        @CHANGEAGENTID,
        @CURRENTAPPUSERID

    if @ISNEW <> 0
    begin
        exec dbo.USP_IDSETREGISTER_GETAPPLOCK @IDSET_ID, 0
    end

    declare @COUNT int = (select count(1) from @IDSET.nodes('/IDSET/ITEM') T(c))
    if @COUNT > 0
    begin
        declare @SQLTOEXEC nvarchar(max)
        set @SQLTOEXEC = 'declare @XML xml = ''' + cast(@IDSET as nvarchar(max)) + ''';'
            + ' insert into dbo.' + @IDSET_TABLENAME
            + ' select T.c.value(''(ID)[1]'',''uniqueidentifier'')'
            + ' from @XML.nodes(''/IDSET/ITEM'') T(c)'
        exec sp_executesql @SQLTOEXEC;
    end

    -- Set the rowcount on the IDSETREGISTER table...

    exec.USP_IDSETREGISTER_SETROWCOUNT @IDSET_ID, @COUNT, @CHANGEAGENTID

    -- Release the applocks using the same connection that created the applocks for this IDSet...

    exec.USP_IDSETREGISTER_RELEASEAPPLOCK @IDSET_ID, 0
end