USP_TASKCATALOG_CREATEORUPDATE

CREATE procedure dbo.USP_TASKCATALOG_CREATEORUPDATE
    @ID uniqueidentifier = null output, 
    @NAME nvarchar(100),
    @DESCRIPTION nvarchar(max) = '',
    @FUNCTIONALAREAID uniqueidentifier,
    @TASKGROUP nvarchar(100) = '',
    @TASKSPECXML xml, 
    @SEQUENCE integer,
    @CHANGEAGENTID uniqueidentifier = null,
    @ISFUNCTIONALAREADEFAULT bit = null
as
begin
    set nocount on

    declare @ExistingID uniqueidentifier

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

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

    if @TASKGROUP is null
        set @TASKGROUP = ''

    if @ISFUNCTIONALAREADEFAULT is null
        set @ISFUNCTIONALAREADEFAULT = 0

    declare @date datetime
    set @date = getDate()

    if @ExistingID is null 

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

            insert into dbo.TASKCATALOG
            (ID, [NAME], DESCRIPTION, FUNCTIONALAREAID, TASKGROUP, TASKSPECXML, SEQUENCE, ISFUNCTIONALAREADEFAULT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values 
            (@ID, @NAME, @DESCRIPTION, @FUNCTIONALAREAID, @TASKGROUP, @TASKSPECXML, @SEQUENCE, @ISFUNCTIONALAREADEFAULT, @ChangeAgentID, @ChangeAgentID, @date, @date)        
        end

    else
        update dbo.TASKCATALOG
            set [NAME] = @NAME,
                DESCRIPTION = @DESCRIPTION,
                FUNCTIONALAREAID = @FUNCTIONALAREAID,
                TASKGROUP = @TASKGROUP,
                TASKSPECXML = @TASKSPECXML,
                SEQUENCE = @SEQUENCE,
                ISFUNCTIONALAREADEFAULT = @ISFUNCTIONALAREADEFAULT,
                CHANGEDBYID = @ChangeAgentID,
                DATECHANGED = @date
            where ID = @ID;

    if @@rowcount=0 return 1;

    return 0;
end