USP_IMPORTSELECTIONPROCESS_CHECKFORDELETEDIDS

Parameters

Parameter	Parameter Type	Mode
@IMPORTTABLENAME	nvarchar(128)	IN
@EXCEPTIONCODE	tinyint	IN
@IDFIELDNAME	nvarchar(128)	IN
@IDFIELDDATATYPE	nvarchar(128)	IN
@BASETABLENAME	nvarchar(128)	IN
@BASETABLEIDFIELDNAME	nvarchar(128)	IN
@ALTERNATELOOKUPIDTYPECODEID	uniqueidentifier	IN

Definition

Copy



create procedure dbo.[USP_IMPORTSELECTIONPROCESS_CHECKFORDELETEDIDS]
(
  @IMPORTTABLENAME nvarchar(128),
  @EXCEPTIONCODE tinyint,
  @IDFIELDNAME nvarchar(128),
  @IDFIELDDATATYPE nvarchar(128),
  @BASETABLENAME nvarchar(128),
  @BASETABLEIDFIELDNAME nvarchar(128),
  @ALTERNATELOOKUPIDTYPECODEID uniqueidentifier = null
)
with execute as owner  -- guarantees that BBAppFxServiceRole can check IDs in @BASETABLENAME even if it does not have SELECT permission to the table

as begin
  set nocount on;

  declare @SQL nvarchar(max);
  declare @PARAMETERS nvarchar(max) = '@EXCEPTIONCODE tinyint';

  set @SQL =
    'update dbo.[' + @IMPORTTABLENAME + '] set ' +
    '  [EXCEPTIONCODE] = @EXCEPTIONCODE ' + 
    'where [EXCEPTIONCODE] = 0 ';

  if @ALTERNATELOOKUPIDTYPECODEID is not null begin
    set @SQL = @SQL +
      'and cast([' + @IDFIELDNAME + '] as ' + @IDFIELDDATATYPE + ') not in ' +
      '(select [ALTERNATELOOKUPID] from dbo.[ALTERNATELOOKUPID] where [ALTERNATELOOKUPIDTYPECODEID] = @ALTERNATELOOKUPIDTYPECODEID)';

    set @PARAMETERS = @PARAMETERS + ', @ALTERNATELOOKUPIDTYPECODEID uniqueidentifier';

    exec sp_executesql @SQL, @PARAMETERS, @EXCEPTIONCODE = @EXCEPTIONCODE, @ALTERNATELOOKUPIDTYPECODEID = @ALTERNATELOOKUPIDTYPECODEID;

  end else begin
    set @SQL = @SQL +
      'and cast([' + @IDFIELDNAME + '] as ' + @IDFIELDDATATYPE + ') not in (select [' + @BASETABLEIDFIELDNAME + '] from dbo.[' + @BASETABLENAME + '])';

    exec sp_executesql @SQL, @PARAMETERS, @EXCEPTIONCODE = @EXCEPTIONCODE;
  end

  return 0;
end