USP_SQLPROCEDURECATALOG_CREATEORUPDATE

CREATE procedure dbo.[USP_SQLPROCEDURECATALOG_CREATEORUPDATE]
    @ID uniqueidentifier = null output, 
    @PROCEDURENAME nvarchar(100),
    @DESCRIPTION nvarchar(max) = '',
    @SQLPROCEDURESPECXML 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.[SQLPROCEDURECATALOG] where [PROCEDURENAME] = @PROCEDURENAME
      set @ID = @ExistingID
    end
else
    select @ExistingID = [ID] from dbo.[SQLPROCEDURECATALOG] 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.[SQLPROCEDURECATALOG]
        ([ID], [PROCEDURENAME], [DESCRIPTION], [SQLPROCEDURESPECXML], [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED], [MARTKEY])
        values 
        (@ID, @PROCEDURENAME, @DESCRIPTION, @SQLPROCEDURESPECXML, @ChangeAgentID, @ChangeAgentID, @date, @date, @MARTKEY)        
    end

else
    update dbo.[SQLPROCEDURECATALOG]
        set [PROCEDURENAME] = @PROCEDURENAME,
            [DESCRIPTION] = @DESCRIPTION,
            [SQLPROCEDURESPECXML] = @SQLPROCEDURESPECXML,
            [CHANGEDBYID] = @ChangeAgentID,
            [DATECHANGED] = @date,
        [MARTKEY] = @MARTKEY
        where [ID] = @ID;

if @@rowcount=0 return 1;

return 0;