USP_TRANSLATIONFUNCTION_CREATEORUPDATE

CREATE procedure dbo.USP_TRANSLATIONFUNCTION_CREATEORUPDATE
    @ID uniqueidentifier = null output,
    @NAME nvarchar(60),
    @DESCRIPTION nvarchar(1000) = '',
    @RECORDTYPE nvarchar(50) = '',
    @FUNCTION nvarchar(128),
    @PARAMETERXML xml = null,
    @SPECXML xml,    
    @CHANGEAGENTID uniqueidentifier = null,
    @IMPLEMENTATIONTYPE tinyint=0,
    @ASSEMBLYNAME nvarchar(128)='',
    @CLASSNAME nvarchar(128)=''

as

set nocount on;

declare @ExistingID uniqueidentifier;
declare @RecTypeID uniqueidentifier;
declare @OBJID int

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

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

if @IMPLEMENTATIONTYPE =0
begin

    -- make sure this is a valid function
    select @OBJID = id from dbo.sysobjects where type in ('FN', 'FS') and name = @FUNCTION;

    if @OBJID is null
        begin
            raiserror ('Scalar function ''%s'' does not exist or you do not have security access.', 16, 1, @FUNCTION)
            return 1;
        end
end


if len(@RECORDTYPE) > 0 
    --Add record type if it does not exist.        
    exec dbo.USP_RECORDTYPE_GETID_OR_CREATE_FROMNAME @NAME=@RECORDTYPE,@CHANGEAGENTID=@CHANGEAGENTID,@RECORDTYPEID=@RecTypeID output;
else
    set @RecTypeID = null

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

if @ExistingID is null 

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

        insert into dbo.TRANSLATIONFUNCTIONCATALOG (ID, [NAME], DESCRIPTION, FUNCTIONNAME, RECORDTYPEID, PARAMETERDEFINITION, TRANSLATIONFUNCTIONSPEC, ADDEDBYID, CHANGEDBYID,IMPLEMENTATIONTYPE,ASSEMBLYNAME,CLASSNAME, DATEADDED, DATECHANGED)
            values (@ID, @NAME, @DESCRIPTION, @FUNCTION, @RecTypeID, @PARAMETERXML, @SPECXML, @CHANGEAGENTID, @CHANGEAGENTID,@IMPLEMENTATIONTYPE,@ASSEMBLYNAME,@CLASSNAME, @CURRENTDATE, @CURRENTDATE)
    end

else

    update dbo.TRANSLATIONFUNCTIONCATALOG 
    set [NAME] = @NAME,
        DESCRIPTION = @DESCRIPTION, 
        FUNCTIONNAME = @FUNCTION,
        RECORDTYPEID = @RecTypeID,
        PARAMETERDEFINITION = @PARAMETERXML,
        TRANSLATIONFUNCTIONSPEC = @SPECXML,
        CHANGEDBYID = @CHANGEAGENTID,
        IMPLEMENTATIONTYPE=@IMPLEMENTATIONTYPE,
        ASSEMBLYNAME=@ASSEMBLYNAME,
        CLASSNAME=@CLASSNAME,
        DATECHANGED = @CURRENTDATE

    where ID = @ExistingID;

return 0;