USP_TABLECATALOG_CREATEORUPDATE

create procedure dbo.USP_TABLECATALOG_CREATEORUPDATE
    @ID uniqueidentifier = null output, 
    @TABLENAME nvarchar(100),
    @DESCRIPTION nvarchar(max) = '',
    @TABLESPECXML xml, 
    @TABLESPECEXTENSIONXML xml, 
    @ISBUILTIN bit=0, 
    @ISCUSTOMIZABLE bit=0, 
    @CHANGEAGENTID uniqueidentifier = null

as

set nocount on

declare @ExistingID uniqueidentifier

if @ID is null
    begin
      select @ExistingID = ID from dbo.TABLECATALOG where TABLENAME = @TABLENAME
      set @ID = @ExistingID
    end
else
    select @ExistingID = ID from dbo.TABLECATALOG 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.TABLECATALOG
        (ID, TABLENAME, DESCRIPTION, TABLESPECXML, TABLESPECEXTENSIONXML, ISBUILTIN, ISCUSTOMIZABLE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values 
        (@ID, @TABLENAME, @DESCRIPTION, @TABLESPECXML, @TABLESPECEXTENSIONXML, @ISBUILTIN, @ISCUSTOMIZABLE, @ChangeAgentID, @ChangeAgentID, @date, @date)        
    end

else
    update dbo.TABLECATALOG
        set TABLENAME=@TABLENAME,
            DESCRIPTION=@DESCRIPTION,
            TABLESPECXML=@TABLESPECXML,
            TABLESPECEXTENSIONXML=@TABLESPECEXTENSIONXML,      
            ISCUSTOMIZABLE=@ISCUSTOMIZABLE,
            CHANGEDBYID=@ChangeAgentID,
            DATECHANGED=@date
        where ID = @ID;

if @@rowcount=0 return 1;

return 0;