USP_FUNCTIONALAREACATALOG_CREATEORUPDATE

CREATE procedure [dbo].[USP_FUNCTIONALAREACATALOG_CREATEORUPDATE]
    @ID uniqueidentifier = null output, 
    @NAME nvarchar(100),
    @DESCRIPTION nvarchar(max) = '',
    @FUNCTIONALAREASPECXML xml, 
    @SEQUENCE integer,
    @CHANGEAGENTID uniqueidentifier = null

as

set nocount on;

declare @ExistingID uniqueidentifier;

if @ID is null
    begin
      select @ExistingID = ID from dbo.FUNCTIONALAREACATALOG where NAME = @NAME;
      set @ID = @ExistingID;
    end
else
    select @ExistingID = ID from dbo.FUNCTIONALAREACATALOG where ID = @ID;

if @ChangeAgentID is null 
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @ChangeAgentID output;

declare @date datetime;
set @date = getDate();

-----------------------------------------------------------

declare @INSTALLEDPRODUCTLISTXML xml;

with xmlnamespaces(default 'bb_appfx_functionalarea', 'bb_appfx_commontypes' as c)    
    select INSTALLEDPRODUCTLISTXML = @FUNCTIONALAREASPECXML.query('/FunctionalAreaSpec/c:InstalledProductList');
-----------------------------------------------------------

if @ExistingID is null 

    begin    
        if @ID is null set @ID = NewID();

        insert into dbo.FUNCTIONALAREACATALOG
        (ID, [NAME], DESCRIPTION, FUNCTIONALAREASPECXML, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, INSTALLEDPRODUCTLISTXML)
        values 
        (@ID, @NAME, @DESCRIPTION, @FUNCTIONALAREASPECXML, @SEQUENCE, @ChangeAgentID, @ChangeAgentID, @date, @date, @INSTALLEDPRODUCTLISTXML);    
    end

else
    update dbo.FUNCTIONALAREACATALOG
        set [NAME] = @NAME,
            DESCRIPTION = @DESCRIPTION,
            FUNCTIONALAREASPECXML = @FUNCTIONALAREASPECXML,
            SEQUENCE = @SEQUENCE,
            CHANGEDBYID = @ChangeAgentID,
            DATECHANGED = @date,
            INSTALLEDPRODUCTLISTXML = @INSTALLEDPRODUCTLISTXML
        where ID = @ID;

if @@rowcount=0 return 1;

return 0;