USP_RECORDOPERATION_CREATEORUPDATE_CLR

CREATE procedure [dbo].[USP_RECORDOPERATION_CREATEORUPDATE_CLR]
(
    @ID uniqueidentifier = null output, 
    @NAME nvarchar(60),
    @DESCRIPTION nvarchar(1000) = '',
    @RECORDOPERATIONSPECXML xml,
    @RECORDTYPE nvarchar(50),
    @OPERATIONTYPE tinyint = 0,
    @ASSEMBLYNAME nvarchar(128),
    @CLASSNAME nvarchar(128),
    @STATICPARAMETERDEFINITION xml = null,
    @TIMEOUTSECONDS int,
    @CHANGEAGENTID uniqueidentifier = null,
    @SPECUINAME nvarchar(60) = N''
)
as

    --this routine registers a CLR based record operation in the catalog


    set nocount on;

    declare @RecTypeID uniqueidentifier;
    declare @ExistingID uniqueidentifier;

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

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

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

  declare @HASID bit;
  set @HASID=
    coalesce(
           @RECORDOPERATIONSPECXML.value(
                                   '
                                   declare namespace bbfa="bb_appfx_recordoperation";
                                   /bbfa:RecordOperationSpec[1]/@HasID
                                   '
                                  ,'bit')
           ,1);

    declare @SECURITYUIFOLDER nvarchar(255);        
    set @SECURITYUIFOLDER= coalesce(@RECORDOPERATIONSPECXML.value(
                                        '
                                        declare namespace bbfa="bb_appfx_recordoperation";
                                        declare namespace c="bb_appfx_commontypes";
                                        /bbfa:RecordOperationSpec[1]/@c:SecurityUIFolder
                                        '
                                        ,'nvarchar(255)'),'');

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

  --Add record type if it does not exist and a record type has been specified.

  if @HASID = 1
      exec dbo.USP_RECORDTYPE_GETID_OR_CREATE_FROMNAME @NAME=@RECORDTYPE,@CHANGEAGENTID=@CHANGEAGENTID,@RECORDTYPEID=@RecTypeID output;

    if @ExistingID is null 

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

            insert into dbo.RECORDOPERATIONCATALOG (ID, DISPLAYNAME, DESCRIPTION, STATICPARAMETERDEFINITION, RECORDOPERATIONSPECXML, IMPLEMENTATIONTYPE, ASSEMBLYNAME, CLASSNAME, RECORDTYPEID, OPERATIONTYPE, TIMEOUTSECONDS, ADDEDBYID, CHANGEDBYID, SECURITYUIFOLDER, DATEADDED, DATECHANGED, SPECUINAME)
                    values (@ID, @NAME, @DESCRIPTION, @STATICPARAMETERDEFINITION, @RECORDOPERATIONSPECXML, 1, @ASSEMBLYNAME, @CLASSNAME, @RecTypeID, @OPERATIONTYPE, @TIMEOUTSECONDS, @CHANGEAGENTID, @CHANGEAGENTID, @SECURITYUIFOLDER, @CURRENTDATE, @CURRENTDATE, @SPECUINAME);
        end

    else

        update dbo.RECORDOPERATIONCATALOG 
            set DISPLAYNAME = @NAME,
            DESCRIPTION = @DESCRIPTION,
            STATICPARAMETERDEFINITION = @STATICPARAMETERDEFINITION,
            RECORDOPERATIONSPECXML = @RECORDOPERATIONSPECXML,
            IMPLEMENTATIONTYPE = 1,
            ASSEMBLYNAME = @ASSEMBLYNAME,
            CLASSNAME = @CLASSNAME,
            OPERATIONTYPE = @OPERATIONTYPE,
            TIMEOUTSECONDS = @TIMEOUTSECONDS,
            CHANGEDBYID = @CHANGEAGENTID,
            SECURITYUIFOLDER = @SECURITYUIFOLDER,
            DATECHANGED = @CURRENTDATE,
            RECORDTYPEID = @RecTypeID,
            SPECUINAME = @SPECUINAME
        where ID = @ExistingID;

    if @@error <> 0 return 1;

    return 0;