USP_SQLVIEWCATALOG_CREATEORUPDATE

CREATE procedure dbo.[USP_SQLVIEWCATALOG_CREATEORUPDATE]
  @ID uniqueidentifier = null output, 
    @VIEWNAME nvarchar(128),
    @DESCRIPTION nvarchar(max) = '',
    @SQLVIEWSPECXML xml, 
    @CHANGEAGENTID uniqueidentifier = null,
  @MARTKEY nvarchar(255) = ''  

as

set nocount on

declare @ExistingID uniqueidentifier

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

    end

else

    update dbo.[SQLVIEWCATALOG]

        set [VIEWNAME] = @VIEWNAME,
            [DESCRIPTION] = @DESCRIPTION,
            [SQLVIEWSPECXML] = @SQLVIEWSPECXML,
            [CHANGEDBYID] = @ChangeAgentID,
            [DATECHANGED] = @date,
        [MARTKEY] = @MARTKEY
        where [ID] = @ID;

if @@rowcount=0 return 1;

return 0;