USP_MKTSEGMENTATIONEXCLUSION_UPDATEQUANTITY

CREATE procedure dbo.USP_MKTSEGMENTATIONEXCLUSION_UPDATEQUANTITY
(
  @SEGMENTATIONEXCLUSIONID uniqueidentifier,
  @EXCEPTIONTABLE nvarchar(128),
  @DELETEDQUANTITY int output,
  @EXCEPTIONQUANTITY int output,
  @CHANGEAGENTID uniqueidentifier = null
)
with execute as owner
as
  set nocount on;

  declare @ORIGINALQUANTITY int;
  declare @CURRENTDATE datetime = getdate();
  declare @DATATABLE nvarchar(128);
  declare @EXCLUSIONTABLE nvarchar(128);
  declare @SQL nvarchar(max);

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

  select
    @DATATABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME]([SEGMENTATIONID]),
    @EXCLUSIONTABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATEEXCLUSION_MAKETABLENAME]([SEGMENTATIONID])
  from dbo.[MKTSEGMENTATIONEXCLUSION]
  where [MKTSEGMENTATIONEXCLUSION].[ID] = @SEGMENTATIONEXCLUSIONID;

  set @SQL = 'select @ORIGINALQUANTITY = count(*) from dbo.[' + @DATATABLE + ']';
  exec sp_executesql @SQL, N'@ORIGINALQUANTITY int output', @ORIGINALQUANTITY = @ORIGINALQUANTITY output;

  set @SQL = 'select @DELETEDQUANTITY = count(*) from dbo.[' + @EXCLUSIONTABLE + '] where [SEGMENTATIONEXCLUSIONID] = @SEGMENTATIONEXCLUSIONID';
  exec sp_executesql @SQL, N'@DELETEDQUANTITY int output, @SEGMENTATIONEXCLUSIONID uniqueidentifier', @DELETEDQUANTITY = @DELETEDQUANTITY output, @SEGMENTATIONEXCLUSIONID = @SEGMENTATIONEXCLUSIONID;

  if isnull(@EXCEPTIONTABLE, '') <> '' and exists(select * from INFORMATION_SCHEMA.TABLES where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @EXCEPTIONTABLE)
    begin
      set @SQL = 'select @EXCEPTIONQUANTITY = count(*) from dbo.[' + @EXCEPTIONTABLE + ']';
      exec sp_executesql @SQL, N'@EXCEPTIONQUANTITY int output', @EXCEPTIONQUANTITY = @EXCEPTIONQUANTITY output;
    end
  else
    set @EXCEPTIONQUANTITY = 0;

  update dbo.[MKTSEGMENTATIONEXCLUSION] set
    [ORIGINALQUANTITY] = @ORIGINALQUANTITY,
    [DELETEDQUANTITY] = @DELETEDQUANTITY, 
    [EXCEPTIONQUANTITY] = @EXCEPTIONQUANTITY,
    [CHANGEDBYID] = @CHANGEAGENTID,
    [DATECHANGED] = @CURRENTDATE
  where [ID] = @SEGMENTATIONEXCLUSIONID;

  return 0;