USP_BUSINESSPROCESSOUTPUTLETTERTEMPLATE_DATALIST_2

CREATE procedure dbo.[USP_BUSINESSPROCESSOUTPUTLETTERTEMPLATE_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, [LETTERTEMPLATENAME] nvarchar(255), [RECORDS] integer);  
  create table #TEMPLETTERTEMPLATEDATA([ID] uniqueidentifier, [WORDTEMPLATEID] uniqueidentifier, [LETTERTEMPLATENAME] nvarchar(255), [RECORDS] integer,[LETTERTEMPLATE] varbinary(max))

 insert into #TEMPLETTERTEMPLATEDATA
 (    
    [ID],
    [WORDTEMPLATEID],
    [LETTERTEMPLATENAME],
    [LETTERTEMPLATE]
 )
 select  
    [ID],
    [WORDTEMPLATEID],
    [LETTERTEMPLATENAME],
    [LETTERTEMPLATE]
 from dbo.[BUSINESSPROCESSOUTPUTTEMPLATE]  
 where [BUSINESSPROCESSSTATUSID] = @BUSINESSPROCESSSTATUSID  and 
 [BUSINESSPROCESSOUTPUTTABLEKEY] = @BUSINESSPROCESSOUTPUTTABLEKEY  


 insert into @TEMPLATES  
 (
    [ID],
    [WORDTEMPLATEID],
    [LETTERTEMPLATENAME]
 ) 
 select 
    [ID],
    [WORDTEMPLATEID],
    [LETTERTEMPLATENAME]
 from #TEMPLETTERTEMPLATEDATA
 where LETTERTEMPLATE 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],  
    [LETTERTEMPLATENAME],  
    [RECORDS]  
  from @TEMPLATES  
  order by [LETTERTEMPLATENAME];  

  IF OBJECT_ID('tempdb..#TEMPLETTERTEMPLATEDATA') IS NOT NULL drop table #TEMPLETTERTEMPLATEDATA
  return 0;