USP_BUSINESSPROCESSOUTPUTLABELTEMPLATE_DATALIST_2

CREATE procedure dbo.[USP_BUSINESSPROCESSOUTPUTLABELTEMPLATE_DATALIST_2]
(
  @BUSINESSPROCESSSTATUSID uniqueidentifier,
  @BUSINESSPROCESSOUTPUTTABLEKEY nvarchar(255)
)
with execute as owner
as
  set nocount on;

  declare @ID uniqueidentifier;
  declare @WORDTEMPLATEID uniqueidentifier;
  declare @OUTPUTTABLE nvarchar(128);
  declare @SQL nvarchar(max);
  declare @RECORDS integer;
  declare @TEMPLATES table ([ID] uniqueidentifier, [WORDTEMPLATEID] uniqueidentifier, [LABELTEMPLATENAME] nvarchar(255), [RECORDS] integer);

  insert into @TEMPLATES
  (
    [ID],
    [WORDTEMPLATEID],
    [LABELTEMPLATENAME]
  )
    select
        [BUSINESSPROCESSOUTPUTTEMPLATE].[ID],
    [BUSINESSPROCESSOUTPUTTEMPLATE].[WORDTEMPLATEID],
        [BUSINESSPROCESSOUTPUTTEMPLATE].[LABELTEMPLATENAME] 
    from dbo.[BUSINESSPROCESSOUTPUTTEMPLATE]
    where [BUSINESSPROCESSOUTPUTTEMPLATE].[BUSINESSPROCESSSTATUSID] = @BUSINESSPROCESSSTATUSID
  and [BUSINESSPROCESSOUTPUTTEMPLATE].[BUSINESSPROCESSOUTPUTTABLEKEY] = @BUSINESSPROCESSOUTPUTTABLEKEY
  and [LABELTEMPLATE] is not null;

  select @OUTPUTTABLE = [TABLENAME] from dbo.[BUSINESSPROCESSOUTPUT] where [BUSINESSPROCESSSTATUSID] = @BUSINESSPROCESSSTATUSID and [TABLEKEY] = @BUSINESSPROCESSOUTPUTTABLEKEY;
  set @SQL = 'select @RECORDS = count(*) from dbo.[' + @OUTPUTTABLE + '] where [WORDTEMPLATEID] = @WORDTEMPLATEID;';

  declare TEMP cursor local fast_forward for
    select [ID], [WORDTEMPLATEID] from @TEMPLATES;

  open TEMP;
  fetch next from TEMP into @ID, @WORDTEMPLATEID;

  while (@@FETCH_STATUS = 0)
    begin
      exec sp_executesql @SQL, N'@RECORDS integer output, @WORDTEMPLATEID uniqueidentifier', @RECORDS = @RECORDS output, @WORDTEMPLATEID = @WORDTEMPLATEID;

      update @TEMPLATES set [RECORDS] = @RECORDS where [ID] = @ID;

      fetch next from TEMP into @ID, @WORDTEMPLATEID;
    end

  close TEMP;
  deallocate TEMP;

  select
    [ID],
    [WORDTEMPLATEID],
        [LABELTEMPLATENAME],
    [RECORDS]
  from @TEMPLATES
  order by [LABELTEMPLATENAME];

  return 0;