USP_IDSETREGISTER_CREATEORUPDATEFORADHOCQUERY

            CREATE procedure dbo.USP_IDSETREGISTER_CREATEORUPDATEFORADHOCQUERY
            (
                @ADHOCQUERYID [uniqueidentifier],
                @USEINQUERYDESIGNER [bit] = 0,
                @STATIC [bit] = 0,
                @NUMROWS [int] = null,
                @CHANGEAGENTID [uniqueidentifier] = null,
                @IDSETREGISTERID uniqueidentifier = null output
            )
            with execute as caller
            as
            set nocount on

                declare @ID uniqueidentifier
                declare @IDSETFUNCTION nvarchar(128)
                declare @IDSETFUNCTIONWITHASOFDATE nvarchar(255)

                select @ID = IDSETREGISTERID from dbo.IDSETREGISTERADHOCQUERY where ADHOCQUERYID = @ADHOCQUERYID

                if @CHANGEAGENTID is null
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

                if @STATIC is null
                    set @STATIC = 0

                if @USEINQUERYDESIGNER is null
                    set @USEINQUERYDESIGNER = 0

                if @ID is null
                    -- add new row to register

                    begin
                        set @IDSETFUNCTION = dbo.UFN_ADHOCQUERY_MAKEIDSETFUNCTIONNAME(@ADHOCQUERYID)
                        set @IDSETFUNCTIONWITHASOFDATE = @IDSETFUNCTION + '_WITHASOFDATE';

                        if @IDSETREGISTERID is null
                            set @IDSETREGISTERID = newid()

                        insert into dbo.IDSETREGISTER (ID, NAME, DESCRIPTION, SITEID, RECORDTYPEID, DBOBJECTNAME, DBOBJECTNAMEWITHASOFDATE, OBJECTTYPE, STATIC, NUMROWS, USEINQUERYDESIGNER, ADDEDBYID, CHANGEDBYID, OWNERID)
                            select @IDSETREGISTERID, ADHOCQUERY.NAME + ' (Ad-hoc Query)', ADHOCQUERY.DESCRIPTION, ADHOCQUERY.SITEID, QUERYVIEWCATALOG.RECORDTYPEID, @IDSETFUNCTION, @IDSETFUNCTIONWITHASOFDATE, '1', @STATIC, @NUMROWS, @USEINQUERYDESIGNER, @CHANGEAGENTID, @CHANGEAGENTID, OWNERID
                            from dbo.ADHOCQUERY
                            join dbo.QUERYVIEWCATALOG on QUERYVIEWCATALOG.ID = ADHOCQUERY.QUERYVIEWCATALOGID
                            where ADHOCQUERY.ID = @ADHOCQUERYID

                        insert into dbo.IDSETREGISTERADHOCQUERY (ID, IDSETREGISTERID, ADHOCQUERYID, ADDEDBYID, CHANGEDBYID) values
                            (newid(), @IDSETREGISTERID, @AdHocQueryID, @ChangeAgentID, @ChangeAgentID)
                    end
                else
                begin
                    set @IDSETREGISTERID = @ID

                    if @USEINQUERYDESIGNER = 0 and exists(select ID from dbo.ADHOCQUERY where dbo.UFN_ADHOCQUERY_IDSETINUSE(@ID, QUERYDEFINITIONXML) = 1)
                    begin
                        raiserror('BBERR_IDSET_INUSEBYADHOCQUERY', 14, 44)
                        raiserror('The ''Show in query designer'' option cannot be set to false because the ID set is in use by another ad-hoc query.', 16, 1)
                        return 1;
                    end

                    -- update existing register info

                    update dbo.IDSETREGISTER 
                        set NAME = ADHOCQUERY.NAME + ' (Ad-hoc Query)', DESCRIPTION = ADHOCQUERY.DESCRIPTION, SITEID = ADHOCQUERY.SITEID, STATIC=@STATIC, NUMROWS=@NUMROWS, USEINQUERYDESIGNER = @USEINQUERYDESIGNER, CHANGEDBYID=@CHANGEAGENTID, DATECHANGED=getDate(), OWNERID = ADHOCQUERY.OWNERID
                        from dbo.ADHOCQUERY where IDSETREGISTER.ID = @ID and ADHOCQUERY.ID = @ADHOCQUERYID
                end