USP_MKTSEGMENTATIONREFRESHPROCESS_GETTOTALRESPONSECOUNTS

CREATE procedure dbo.USP_MKTSEGMENTATIONREFRESHPROCESS_GETTOTALRESPONSECOUNTS
(
  @SEGMENTATIONID uniqueidentifier
)
as
  set nocount on;

  declare @NORMALGIFTIDSETREGISTEROBJECTNAME nvarchar(128);
  declare @UNRESOLVEDGIFTIDSETREGISTERID nvarchar(128);
  declare @COUNT int;
  declare @SQL nvarchar(255);
  declare @PARAMDEF nvarchar(255);
  declare @TEMPTABLE table ([TOTALRESPONSES] int);

  declare GIFTIDSETCURSOR cursor local fast_forward for
    select
      (select [DBOBJECTNAME] from dbo.[IDSETREGISTER] where [ID] = [MKTSEGMENTATIONACTIVATE].[NORMALGIFTIDSETREGISTERID]) as [NORMALGIFTIDSETREGISTEROBJECTNAME],
      (select [DBOBJECTNAME] from dbo.[IDSETREGISTER] where [ID] = [MKTSEGMENTATIONACTIVATE].[UNRESOLVEDGIFTIDSETREGISTERID]) as [UNRESOLVEDGIFTIDSETREGISTEROBJECTNAME]
    from dbo.[MKTSEGMENTATIONACTIVATE] 
    where [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = @SEGMENTATIONID;

  open GIFTIDSETCURSOR;
  fetch next from GIFTIDSETCURSOR into @NORMALGIFTIDSETREGISTEROBJECTNAME, @UNRESOLVEDGIFTIDSETREGISTERID;

  set @PARAMDEF = '@COUNT int output';

  set @COUNT = 0;
  while (@@FETCH_STATUS = 0)
    begin
      set @SQL = 'select @COUNT = @COUNT + count([ID]) from dbo.[' + @NORMALGIFTIDSETREGISTEROBJECTNAME + ']';                    
      exec sp_executesql @SQL, @PARAMDEF, @COUNT = @COUNT output;

      set @SQL = 'select @COUNT = @COUNT + count([ID]) from dbo.[' + @UNRESOLVEDGIFTIDSETREGISTERID + ']';
      exec sp_executesql @SQL, @PARAMDEF, @COUNT = @COUNT output;

      fetch next from GIFTIDSETCURSOR into @NORMALGIFTIDSETREGISTEROBJECTNAME, @UNRESOLVEDGIFTIDSETREGISTERID;
    end

  close GIFTIDSETCURSOR;
  deallocate GIFTIDSETCURSOR;

  insert into @TEMPTABLE values (@COUNT);

  select 
    [TOTALRESPONSES]
  from
    @TEMPTABLE;

  return 0;