Reformat SQL - so SSRS can read the columns

USE [database]
GO
/****** Object:  StoredProcedure [dbo].[spGetEmailListByClientSiteCode]    Script Date: 3/23/2017 1:58:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[spGetEmailListByClientSiteCode] 
@cSiteID varchar(Max)
,@StartDate datetime = '1/1/1970'
,@EndDate datetime = '1/1/9999'

AS

if OBJECT_ID('tempdb.dbo.#tmpEmail') is not null
	drop table #tmpEmail
if OBJECT_ID('tempdb.dbo.#tmpConcat') is not null
	drop table #tmpConcat
if OBJECT_ID('tempdb.dbo.#tmpPivot')  is not null
	drop table #tmpPivot		
	
	DECLARE @SQL varchar(max)
	SET NOCOUNT ON;


select distinct
     entity.entityid,
	 tc.cName,
     tc.cFEIN,	
	 'something' as Link
	,email.subject 
	,email.sent
	,email.EmailDate	
    ,isnull(email.fileId,0) fileId
	,xAddType.EmailAddressTypeId
	,addr.EmailAddress
	,case
		when ea.[file] like '%Attachment(s).%'
	then
		1
	else
		0
	end as AttachCnt
into #tmpEmail
from dbo.email
	inner join EmailXEmailAddressType xAddType on xAddType.EmailId = email.EmailId
	inner join EmailAddressType aType on aType.EmailAddressTypeId = xAddType.EmailAddressTypeId
	inner join EmailAddress addr on addr.EmailAddressId = xAddType.EmailAddressId
	left join EntityXEmail entity on entity.emailId = email.emailid
	left join cadoc_system.dbo.tSite on tSite.nid = entity.entityid
	left join emailattachment ea on ea.emailid = email.emailid

inner join 

(SELECT distinct cadoc_system.dbo.tSite.cSiteCode, cadoc_system.dbo.tSite.cSiteName, cadoc_crm.dbo.tClient.cFEIN, cadoc_crm.dbo.tClient.cName

FROM cadoc_system.dbo.tSite 
INNER JOIN cadoc_system.dbo.tSiteXCrmClient ON cadoc_system.dbo.tSite.nid = cadoc_system.dbo.tSiteXCrmClient.nidSite 
INNER JOIN cadoc_crm.dbo.tClient ON cadoc_system.dbo.tSiteXCrmClient.nIdClient = cadoc_crm.dbo.tClient.nid 
WHERE tClient.cType = 'Client' and cPSiteCode != '')tc

on tc.cSiteCode = entity.EntityId

where EmailDate between @StartDate and @EndDate 
and tc.cSiteCode = @cSiteID or @cSiteID is null 
--and email.fileId is not null and EntityId is not null
order by 1 


	--Concatenate the email addresses
	SELECT p1.EntityId,p1.cName,p1.cFEIN,p1.Link,emailaddresstypeid, subject,EmailDate, fileId, sent, attachcnt,
		  (SELECT emailaddress + ', ' 
			 FROM #tmpemail p2
			 WHERE p2.emailaddresstypeid = p1.emailaddresstypeid
			 and p2.fileId = p1.fileId
			 ORDER BY emailaddress
			 FOR XML PATH('')
		  ) AS emails
	into #tmpConcat           
	FROM #tmpemail p1
	GROUP BY EntityId,cName,cFEIN,Link, emailaddresstypeid, subject,EmailDate, fileId, sent, attachcnt
	
	
	--pivot results into separate From, To and CC fields
	select EntityId,cName,cFEIN,Link, subject , EmailDate, fileId, sent, attachcnt
	,[1] as [From]
	,[2] as [To]
	,[3] as [CC]
	,[4] as [BCC]
	,[5] as [Cataloged]
	into #tmpPivot
	from #tmpConcat
	pivot
	( max(emails)
	for EmailAddressTypeId in ([1],[2],[3],[4],[5])
	) as PivotTable                      


	--Strip trailing comma off From, To, CC, BCC & Cataloged
	select EntityId as SiteCode,cName as ClientName, cFEIN as ClientId, Link as EmailLink,subject as EmailSubject,EmailDate as EmailSentDate,fileID  as nDocument,sent AttachCnt,
	coalesce(cast(left([from],len([from])-1) as text),'') as [from],
	coalesce(cast(left([to],len([to])-1) as text),'') as [To],
	coalesce(cast(left(cc,len(cc)-1) as text),'') as CC,
	coalesce(cast(left(bcc,len(bcc)-1) as text),'') as BCC,
	coalesce(cast(left(Cataloged,len(Cataloged)-1) as text),'') as Cataloged,
	sent
	from #tmpPivot
	order by EntityId

    if OBJECT_ID('tempdb.dbo.#tmpEmail') is not null
        drop table #tmpEmail
    if OBJECT_ID('tempdb.dbo.#tmpConcat') is not null
        drop table #tmpConcat
    if OBJECT_ID('tempdb.dbo.#tmpPivot')  is not null
        drop table #tmpPivot
    		

Select allOpen in new window