USP_RECORDOPERATION_CREATEORUPDATE

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

  set nocount on;

  declare @ExistingID uniqueidentifier;
  declare @OBJID int;
  declare @PROMPTOBJID int;
  declare @ParamName nvarchar(128);
  declare @RecTypeID uniqueidentifier;


  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;



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


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

  -- make sure this is a valid procedure

  select @OBJID = id from dbo.sysobjects where type in ('P', 'PC') and name = @PROCEDURE;

  if @OBJID is null begin

    raiserror ('Procedure ''%s'' does not exist or you do not have security access.', 16, 1, @PROCEDURE);

    return 1;
  end


  --make sure there is an ID param if HasID is true

  if @HASID = 1
  begin
    set @ParamName = null;
    select @ParamName = [name] from sys.parameters where object_id = @OBJID and [name] = '@ID'; -- (pdg ID does not have to be uniqueidentifier)  and [system_type_id] = 36;


    if @ParamName is null begin
      raiserror ('Procedure ''%s'' must have an @ID parameter.', 16, 1, @PROCEDURE);
      return 2;
    end
  end

  -- make sure there is a CHANGEAGENT param

  set @ParamName = null
  select @ParamName = [name] from sys.parameters where object_id = @OBJID and [name] = '@CHANGEAGENTID' and [system_type_id] = 36;

  if @ParamName is null begin
    raiserror ('Procedure ''%s'' must have an @CHANGEAGENTID parameter as uniqueidentifier.', 16, 1, @PROCEDURE);
    return 2;
  end


  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)')
           ,'');


  -- TMV 03/09/2007 Record type may change during an update, moved this out of the if..else

  --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;


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

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


    insert into dbo.RECORDOPERATIONCATALOG (ID, DISPLAYNAME, DESCRIPTION, RECORDOPERATIONSPECXML, EXCEPTIONSXML, RECORDTYPEID, OPERATIONTYPE, IMPLEMENTATIONTYPE, PROCEDURENAME, TIMEOUTSECONDS, ADDEDBYID, CHANGEDBYID,SECURITYUIFOLDER, DATEADDED, DATECHANGED, SPECUINAME)
    values (@ID, @NAME, @DESCRIPTION, @RECORDOPERATIONSPECXML,     @EXCEPTIONSXML, @RecTypeID, @OPERATIONTYPE, 0, @PROCEDURE, @TIMEOUTSECONDS, @CHANGEAGENTID, @CHANGEAGENTID,@SECURITYUIFOLDER, @CURRENTDATE, @CURRENTDATE, @SPECUINAME);


  end else begin

    update dbo.RECORDOPERATIONCATALOG set
      DISPLAYNAME = @NAME,
      DESCRIPTION = @DESCRIPTION,

      STATICPARAMETERDEFINITION = null,
      RECORDOPERATIONSPECXML = @RECORDOPERATIONSPECXML,

      EXCEPTIONSXML = @EXCEPTIONSXML,

      RECORDTYPEID = @RecTypeID,
      IMPLEMENTATIONTYPE = 0,
      PROCEDURENAME = @PROCEDURE,
      OPERATIONTYPE = @OPERATIONTYPE,
      TIMEOUTSECONDS = @TIMEOUTSECONDS,
      CHANGEDBYID = @CHANGEAGENTID,
      SECURITYUIFOLDER=@SECURITYUIFOLDER,
      DATECHANGED = @CURRENTDATE,
      SPECUINAME = @SPECUINAME

    where
      ID = @ExistingID;
  end
end