USP_RECORDSECURITYIDMAP_REGISTERMAPPINGFUNCTION

CREATE procedure dbo.[USP_RECORDSECURITYIDMAP_REGISTERMAPPINGFUNCTION]
(
  @FROMRECORDTYPE nvarchar(50),
  @TORECORDTYPE nvarchar(50),
  @MAPPINGFUNCTION nvarchar(255),
  @CHANGEAGENTID uniqueidentifier = null,
  @MAPPINGFUNCTIONFORQUERY nvarchar(255) = ''
)
as
  set nocount on;

  declare @FORQUERY bit;
  declare @ISTVF bit;
  declare @CURRENTDATE datetime = getdate();

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

  --When saving a mapping function for query, the @MAPPINGFUNCTION param will be empty, and the @MAPPINGFUNCTIONFORQUERY will have

  --the function name, so we need to make sure we don't overwrite the MAPPINGFUNCTION field or the ISTVF field for the normal function.

  if isnull(@MAPPINGFUNCTIONFORQUERY, '') = ''
    begin
      --Could be either an insert or update...

      set @FORQUERY = 0;
      set @ISTVF = isnull((select case when DATA_TYPE = 'TABLE' then 1 else 0 end from INFORMATION_SCHEMA.ROUTINES where ROUTINE_TYPE='FUNCTION' and ROUTINE_NAME = @MAPPINGFUNCTION),0);
    end
  else
    begin
      --Will only execute the update below...

      set @FORQUERY = 1;
      set @MAPPINGFUNCTION = '';
    end

  update dbo.[RECORDSECURITYIDMAP] set 
    [MAPPINGFUNCTION] = (case when @FORQUERY = 0 then @MAPPINGFUNCTION else [MAPPINGFUNCTION] end),
    [MAPPINGFUNCTIONFORQUERY] = (case when @FORQUERY = 1 then @MAPPINGFUNCTIONFORQUERY else [MAPPINGFUNCTIONFORQUERY] end),
    [ISTVF] = (case when @FORQUERY = 0 then @ISTVF else [ISTVF] end),
    [CHANGEDBYID] = @CHANGEAGENTID,
    [DATECHANGED] = @CURRENTDATE
  where [FROMRECORDTYPE] = @FROMRECORDTYPE
  and [TORECORDTYPE] = @TORECORDTYPE;

  if @@ROWCOUNT = 0
    --The insert will only ever happen for non-query mapping functions, so we don't need to set the MAPPINGFUNCTIONFORQUERY field here...

    insert into dbo.[RECORDSECURITYIDMAP] (
      [MAPPINGFUNCTION],
      [FROMRECORDTYPE],
      [TORECORDTYPE],
      [ISTVF],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    ) values (
      @MAPPINGFUNCTION,
      @FROMRECORDTYPE,
      @TORECORDTYPE,
      @ISTVF,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );

  return 0;