USP_SQLFUNCTIONCATALOG_CREATEORUPDATE

CREATE procedure dbo.[USP_SQLFUNCTIONCATALOG_CREATEORUPDATE]
    @ID uniqueidentifier = null output, 
    @FUNCTIONNAME nvarchar(100),
    @DESCRIPTION nvarchar(max) = '',
    @SQLFUNCTIONSPECXML xml, 
    @CHANGEAGENTID uniqueidentifier = null,
  @MARTKEY nvarchar(255) = null

as

set nocount on


declare @ExistingID uniqueidentifier

if @ID is null
    begin
      select @ExistingID = [ID] from dbo.[SQLFUNCTIONCATALOG] where [FUNCTIONNAME] = @FUNCTIONNAME
      set @ID = @ExistingID
    end
else
    select @ExistingID = [ID] from dbo.[SQLFUNCTIONCATALOG] 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.[SQLFUNCTIONCATALOG]
          ([ID], [FUNCTIONNAME], [DESCRIPTION], [SQLFUNCTIONSPECXML], [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED], [MARTKEY])
        values 
          (@ID, @FUNCTIONNAME, @DESCRIPTION, @SQLFUNCTIONSPECXML, @ChangeAgentID, @ChangeAgentID, @date, @date, @MARTKEY)        
    end

else
    update dbo.[SQLFUNCTIONCATALOG]
        set [FUNCTIONNAME] = @FUNCTIONNAME,
            [DESCRIPTION] = @DESCRIPTION,
            [SQLFUNCTIONSPECXML] = @SQLFUNCTIONSPECXML,
            [CHANGEDBYID] = @ChangeAgentID,
            [DATECHANGED] = @date,
        [MARTKEY] = @MARTKEY
        where ID = @ID;

if @@rowcount=0 return 1;

return 0;