USP_CODETABLE_CREATEORUPDATE

CREATE procedure dbo.USP_CODETABLE_CREATEORUPDATE
    @ID uniqueidentifier = null output, 
    @DBTABLENAME nvarchar(100), 
    @CODETABLENAME nvarchar(100), 
    @CODETABLESPECXML xml,
    @ISBUILTIN bit, 
    @CATEGORYID uniqueidentifier, 
    @ChangeAgentID uniqueidentifier=null,
    @SPECUINAME nvarchar(100) = N''
as

set nocount on

if @DBTABLENAME is null or @DBTABLENAME = ''
    begin
        raiserror ('DBTableName was missing or blank', 16,1)
        return 5        
    end

if @CODETABLENAME is null or @CODETABLENAME = ''
    begin
        raiserror ('CodeTableName was missing or blank', 16,1)
        return 5        
    end

if @CATEGORYID is null 
    begin
        raiserror ('CategoryID was missing or blank', 16,1)
        return 5        
    end

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

declare @ExistingID uniqueidentifier

if @ID is null
    begin
      select @ExistingID = ID from dbo.CODETABLECATALOG where DBTABLENAME = @DBTABLENAME
      set @ID = @ExistingID
    end
else
    select @ExistingID = ID from dbo.CODETABLECATALOG 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.CODETABLECATALOG
        (ID, DBTABLENAME, CODETABLENAME, CODETABLESPECXML, ISBUILTIN, CATEGORYID, SPECUINAME, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values 
        (@ID, @DBTABLENAME, @CODETABLENAME, @CODETABLESPECXML, @ISBUILTIN, @CATEGORYID, @SPECUINAME, @ChangeAgentID, @ChangeAgentID, @date, @date)
    end

else
    update dbo.CODETABLECATALOG
        set CODETABLENAME=@CODETABLENAME,
            CATEGORYID=@CATEGORYID,
            CODETABLESPECXML=@CODETABLESPECXML,
            SPECUINAME=@SPECUINAME,
            CHANGEDBYID=@ChangeAgentID,
            DATECHANGED=@date
        where ID = @ID;

if @@rowcount=0 return 3;

return 0;