USP_REPORTCATALOG_CREATEORUPDATE

CREATE procedure dbo.USP_REPORTCATALOG_CREATEORUPDATE
    @ID uniqueidentifier = null output, 
    @NAME nvarchar(100),
    @DESCRIPTION nvarchar(max) = '',
    @REPORTSPECXML xml, 
    @CHANGEAGENTID uniqueidentifier = null,
    @SPECUINAME nvarchar(100) = N''

as

set nocount on;

declare @ExistingID uniqueidentifier;

if @SPECUINAME is null
    set @SPECUINAME = N'';

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

else
    update dbo.REPORTCATALOG
        set [NAME]=@NAME,
            DESCRIPTION=@DESCRIPTION,
            REPORTSPECXML = @REPORTSPECXML,
            CHANGEDBYID=@ChangeAgentID,
            DATECHANGED=@date,
            SPECUINAME=@SPECUINAME
        where ID = @ID;

if @@rowcount=0 return 1;

return 0;