USP_IDSETREGISTER_CREATEORUPDATEFORSMARTQUERYINSTANCE

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

                declare @ID uniqueidentifier
                declare @DBOBJECTNAME nvarchar(128)
                declare @OBJECTTYPE tinyint

                set @OBJECTTYPE = 0

                select @ID = IDSETREGISTERID from dbo.IDSETREGISTERSMARTQUERYINSTANCE where SMARTQUERYINSTANCEID = @SMARTQUERYINSTANCEID

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

                if @USEINQUERYDESIGNER is null
                    set @USEINQUERYDESIGNER = 0


                if @ISSTATIC = 1
                begin
                    set @DBOBJECTNAME = dbo.UFN_SMARTQUERYINSTANCE_MAKEIDSETSTATICTABLENAME(@SMARTQUERYINSTANCEID)
                end
                else
                begin
                    set @DBOBJECTNAME = dbo.UFN_SMARTQUERYINSTANCE_MAKEIDSETFUNCTIONNAME(@SMARTQUERYINSTANCEID)
                    set @OBJECTTYPE = 1
                end

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

                    begin

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

                        insert into dbo.IDSETREGISTER (ID, NAME, DESCRIPTION, SITEID, RECORDTYPEID, DBOBJECTNAME, OBJECTTYPE, STATIC, NUMROWS, USEINQUERYDESIGNER, ADDEDBYID, CHANGEDBYID, OWNERID)
                            select @IDSetRegisterID, SMARTQUERYINSTANCE.NAME + ' (Smart Query)', SMARTQUERYINSTANCE.DESCRIPTION, SMARTQUERYINSTANCE.SITEID, SMARTQUERYCATALOG.RECORDTYPEID, @DBOBJECTNAME, @OBJECTTYPE, @ISSTATIC, @NUMROWS, @USEINQUERYDESIGNER, @CHANGEAGENTID, @CHANGEAGENTID, OWNERID
                            from dbo.SMARTQUERYINSTANCE
                            join dbo.SMARTQUERYCATALOG on SMARTQUERYCATALOG.ID = SMARTQUERYINSTANCE.SMARTQUERYCATALOGID
                            where SMARTQUERYINSTANCE.ID = @SMARTQUERYINSTANCEID

                        insert into dbo.IDSETREGISTERSMARTQUERYINSTANCE (ID, IDSETREGISTERID, SMARTQUERYINSTANCEID, ADDEDBYID, CHANGEDBYID) values
                            (newid(), @IDSETREGISTERID, @SMARTQUERYINSTANCEID, @ChangeAgentID, @ChangeAgentID)
                    end
                else
                    set @IDSETREGISTERID = @ID
                    -- update existing register info

                    update dbo.IDSETREGISTER 
                        set NAME = SMARTQUERYINSTANCE.NAME + ' (Smart Query)', DESCRIPTION = SMARTQUERYINSTANCE.DESCRIPTION, SITEID = SMARTQUERYINSTANCE.SITEID, OBJECTTYPE = @OBJECTTYPE, STATIC=@ISSTATIC, NUMROWS=@NUMROWS, USEINQUERYDESIGNER = @USEINQUERYDESIGNER, CHANGEDBYID=@CHANGEAGENTID, DATECHANGED=getdate(),
                        DBOBJECTNAME = @DBOBJECTNAME, OWNERID = SMARTQUERYINSTANCE.OWNERID
                        from dbo.SMARTQUERYINSTANCE where IDSETREGISTER.ID = @ID and SMARTQUERYINSTANCE.ID = @SMARTQUERYINSTANCEID