USP_GLOBALCHANGECATALOG_CREATEORUPDATE

Creates or updates global change definitions in the GlobalChangeCatalog table.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@PROCEDURENAME	nvarchar(100)	IN
@DESCRIPTION	nvarchar(max)	IN
@GLOBALCHANGESPECXML	xml	IN
@FORMMETADATAXML	xml	IN
@DISPLAYNAME	nvarchar(100)	IN
@CHANGEAGENTID	uniqueidentifier	IN
@GLOBALCHANGEFOLDER	nvarchar(500)	IN

Definition

Copy



            CREATE procedure dbo.USP_GLOBALCHANGECATALOG_CREATEORUPDATE
                    @ID uniqueidentifier = null output, 
                    @PROCEDURENAME nvarchar(100) = '',
                    @DESCRIPTION nvarchar(max) = '',
                    @GLOBALCHANGESPECXML xml, 
                    @FORMMETADATAXML xml,
                    @DISPLAYNAME nvarchar(100),
                    @CHANGEAGENTID uniqueidentifier = null,
                    @GLOBALCHANGEFOLDER nvarchar(500) = ''
                as

                set nocount on

                declare @ExistingID uniqueidentifier

                if @ID is null
                    begin
                      select @ExistingID = ID from dbo.GLOBALCHANGECATALOG where DISPLAYNAME = @DISPLAYNAME
                      set @ID = @ExistingID
                    end
                else
                    select @ExistingID = ID from dbo.GLOBALCHANGECATALOG where ID = @ID

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

                declare @date datetime
                set @date = getDate()

                if @ExistingID is null 

                    begin    
                        if @ID is null set @ID = NewID()

                        insert into dbo.GLOBALCHANGECATALOG
                            (ID, PROCEDURENAME, DESCRIPTION, GLOBALCHANGESPECXML, FORMMETADATAXML, DISPLAYNAME, GLOBALCHANGEFOLDER, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                        values 
                            (@ID, @PROCEDURENAME, @DESCRIPTION, @GLOBALCHANGESPECXML, @FORMMETADATAXML, @DISPLAYNAME, @GLOBALCHANGEFOLDER, @CHANGEAGENTID, @CHANGEAGENTID, @DATE, @DATE)        
                    end

                else
                    update 
                        dbo.GLOBALCHANGECATALOG
                    set 
                        PROCEDURENAME = @PROCEDURENAME,
                        DESCRIPTION = @DESCRIPTION,
                        GLOBALCHANGESPECXML = @GLOBALCHANGESPECXML,
                        FORMMETADATAXML = @FORMMETADATAXML, 
                        DISPLAYNAME = @DISPLAYNAME,    
                        GLOBALCHANGEFOLDER = @GLOBALCHANGEFOLDER,
                        CHANGEDBYID = @CHANGEAGENTID,                            
                        DATECHANGED = @DATE
                    where 
                        ID = @ID;

                if @@rowcount = 0 return 1;

                return 0;