USP_PACKAGECATALOG_CREATEORUPDATE

create procedure dbo.USP_PACKAGECATALOG_CREATEORUPDATE
    @ID uniqueidentifier = null output, 
    @PACKAGENAME nvarchar(100),
    @DESCRIPTION nvarchar(max) = '',
    @PACKAGESPECXML xml, 
    @CHANGEAGENTID uniqueidentifier = null

as

set nocount on

declare @ExistingID uniqueidentifier

if @ID is null
    begin
      select @ExistingID = ID from dbo.PACKAGECATALOG where PACKAGENAME = @PACKAGENAME
      set @ID = @ExistingID
    end
else
    select @ExistingID = ID from dbo.PACKAGECATALOG 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.PACKAGECATALOG
        (ID, PACKAGENAME, DESCRIPTION, PACKAGESPECXML, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values 
        (@ID, @PACKAGENAME, @DESCRIPTION, @PACKAGESPECXML, @ChangeAgentID, @ChangeAgentID, @date, @date)        
    end

else
    update dbo.PACKAGECATALOG
        set PACKAGENAME=@PACKAGENAME,
            DESCRIPTION=@DESCRIPTION,
            PACKAGESPECXML=@PACKAGESPECXML,
            CHANGEDBYID=@ChangeAgentID,
            DATECHANGED=@date
        where ID = @ID;

if @@rowcount=0 return 1;

return 0;