USP_IDSETREGISTERDYNAMICIDSET_CREATEORUPDATE

        create procedure dbo.USP_IDSETREGISTERDYNAMICIDSET_CREATEORUPDATE
        (
            @IDSETREGISTERID uniqueidentifier,
            @CHANGEAGENTID uniqueidentifier,
            @STATICSELECTIONID uniqueidentifier output,
            @STATICSELECTIONNAME nvarchar(300) = null,
            @STATICSELECTIONDESCRIPTION nvarchar(1024) = null
        )
        -- Must execute this SP as owner since we're creating new tables.

        with execute as owner
        as 
            begin
                set nocount on;

                declare @DYNAMICDBOBJNAME nvarchar(128);

                select
                    @DYNAMICDBOBJNAME = IDSETREGISTER.[DBOBJECTNAME]
                from
                    dbo.IDSETREGISTER
                where
                    IDSETREGISTER.[ID] = @IDSETREGISTERID;

                declare @IDSETTABLENAME nvarchar(128) = N'IDSETREGISTERDYNAMICIDSET' + replace(@IDSETREGISTERID, '-', N'') + N'_STATICIDSET';

                -- If the table already exists, just delete everything in it.

                if object_id(N'dbo.' + @IDSETTABLENAME) is not null
                begin
                    declare @TRUNCATETABLESQL nvarchar(max) = N'truncate table [dbo].[' + @IDSETTABLENAME + N']';
                    exec dbo.sp_executesql @TRUNCATETABLESQL;
                end
                else
                begin
                    declare @CREATETABLESQL nvarchar(max) = N'create table [dbo].[' + @IDSETTABLENAME + N']([ID] uniqueidentifier)';
                    exec dbo.sp_executesql @CREATETABLESQL ;
                end

                -- Insert data into the static selection table:

                declare @INSERTSQL nvarchar(max) = N'insert into [dbo].[' + @IDSETTABLENAME + N']([ID])' + char(13) +
                        N'select [ID] from dbo.' + @DYNAMICDBOBJNAME + N'()';

                exec sp_executesql @INSERTSQL;

                -- Now that the physical table is created, create a function that wraps that table.

                declare @FUNCTIONNAME nvarchar(128) = N'UFN_' + @IDSETTABLENAME;
                declare @FUNCTIONEXISTS bit = 0;
                declare @CREATEFUNCTIONSQL nvarchar(max) = 'create function';
                if object_id(N'dbo.' + @FUNCTIONNAME) is not null
                    set @CREATEFUNCTIONSQL = 'alter function'

                set @CREATEFUNCTIONSQL = @CREATEFUNCTIONSQL + N' dbo.' + @FUNCTIONNAME + N'()';
                set @CREATEFUNCTIONSQL = @CREATEFUNCTIONSQL + char(13) + N'returns table as return' + char(13);
                set @CREATEFUNCTIONSQL = @CREATEFUNCTIONSQL + N'select ID from dbo.' + @IDSETTABLENAME;

                exec dbo.sp_executesql @CREATEFUNCTIONSQL;

                -- Insert into IDSETREGISTER:

                    select
                        @STATICSELECTIONID = IDSETREGISTER.[ID]
                    from
                        dbo.IDSETREGISTER
                    where IDSETREGISTER.[DBOBJECTNAME] = @FUNCTIONNAME;

                declare @NUMROWS int;
                declare @RECORDTYPEID uniqueidentifier;
                declare @IDSETNAME nvarchar(300);
                declare @IDSETDESCRIPTION nvarchar(1024);
                select 
                    @NUMROWS = IDSETREGISTER.[NUMROWS],
                    @RECORDTYPEID = IDSETREGISTER.[RECORDTYPEID],
                    @IDSETNAME = IDSETREGISTER.[NAME] + N' (Static selection)',
                    @IDSETDESCRIPTION = N'Selection for ' + IDSETREGISTER.[NAME]
                from IDSETREGISTER where IDSETREGISTER.[ID] = @IDSETREGISTERID;
                if @STATICSELECTIONNAME is null
                    set @STATICSELECTIONNAME = @IDSETNAME;

                if @STATICSELECTIONDESCRIPTION is null
                    set @STATICSELECTIONDESCRIPTION = @IDSETDESCRIPTION;

                exec dbo.USP_IDSETREGISTER_CREATEORUPDATE
                    @STATICSELECTIONID output,
                    @STATICSELECTIONNAME,
                    @STATICSELECTIONDESCRIPTION,
                    @FUNCTIONNAME,
                    0,
                    @RECORDTYPEID,
                    0,
                    1,
                    @NUMROWS,
                    @CHANGEAGENTID;                

                -- Add an entry to the join table linking the static and dynamic selection:

                declare @IDSETREGISTERDYNAMICIDSETID uniqueidentifier = null;
                select
                    @IDSETREGISTERDYNAMICIDSETID = IDSETREGISTERDYNAMICIDSET.[ID]
                from
                    dbo.IDSETREGISTERDYNAMICIDSET
                where
                    IDSETREGISTERDYNAMICIDSET.[DYNAMICIDSETREGISTERID] = @IDSETREGISTERID and
                    IDSETREGISTERDYNAMICIDSET.[STATICIDSETREGISTERID] = @STATICSELECTIONID;

                if @IDSETREGISTERDYNAMICIDSETID is null
                begin
                    declare @CURRENTDATE datetime = getdate();
                    insert into dbo.IDSETREGISTERDYNAMICIDSET
                    (
                        [DYNAMICIDSETREGISTERID], 
                        [STATICIDSETREGISTERID],
                        [ADDEDBYID],
                        [CHANGEDBYID],
                        [DATEADDED],
                        [DATECHANGED]
                    )
                    values(
                        @IDSETREGISTERID, 
                        @STATICSELECTIONID,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CURRENTDATE,
                        @CURRENTDATE
                    );
                end
            return 0;
            end