SQLTeam.com | Weblogs | Forums

Email Excel through a SQL Store Procedure Job


#1

I have this SP below,

Our client would like to have this email go out with an excel file attached with the results. Can this be done through a SP like this? If not, what can I do to change this to make it work? If it has to be CSV file, I believe that should be okay too.

Any Help would be greatly appreciated, Thanks!

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
DECLARE @NoOfRows INT

set deadlock_priority high;

SET @NoOfRows = (SELECT Count(distinct c.clm_id1)
from impact.dbo.clm as c
INNER JOIN impact.dbo.P109A6 as p on p.clm_id1 = c.clm_id1
LEFT JOIN impact.dbo.clme NEGHR ON c.clm_id1 = NEGHR.clme_id and NEGHR.clme_fild = '%NEGHR'
LEFT JOIN impact.dbo.clme NGDNY on c.clm_id1 = NGDNY.clme_id and NGDNY.clme_fild = '%NGDNY'
LEFT JOIN impact.dbo.clme NGAPR on c.clm_id1 = NGAPR.clme_id and NGAPR.clme_fild = '%NGAPR'
join Impact.dbo.vw_valid_302 on val_code = clm_spc
Where
clm_cc1 = 13500 and
clm_stades = 'HOLD' and NGDNY.clme_fild is null and NGAPR.clme_fild is null)

--print @COALINoOfRowsnj , u l.......jkjkjkjkjkv7
Print 'HealthNow Claims'
Print @NoOfRows
if @NoOfRows > 0
begin

DECLARE @tableHTML NVARCHAR(MAX) ;

SET @tableHTML =
--clm_id1, clm_1a, clm_1a1, clm_1a2, clm_rcvd, clm_stades, clm_inet, clm_att1, clm_att2, clm_att3, clm_att4, clm_att5, clm_chast, clm_5, clm_1, clm_batch, clm_65a
N'

HealthNOW (DIG plan only)

' +
N'

Number of Claims ' + convert(nvarchar(5),@NoOfRows) + '' +
N'

' +
---N'
' +
N'' +
N'' +
N'' +
CAST ( ( SELECT Distinct td = c.clm_id1 , '',
td =case when p.clm_12a IS null then '' else p.clm_12a end , '',
td = case when p.clm_12b IS null then '' else p.clm_12b end , '',
td =Convert(varchar(12),p.clm_14,101) , '',

td =case when p.clm_38a IS null then '' else p.clm_38a end , '',
td = case when p.clm_38b IS null then '' else p.clm_38b end , '',
td = Convert(varchar(12),c.clm_6a,101), '',
td = Convert(varchar(12),c.clm_6b,101), '',
td =case when c.clm_1a IS null then '' else c.clm_1a end , '',
td =case when c.clm_1a1 IS null then '' else c.clm_1a1 end , '',
td =case when c.clm_1a2 IS null then '' else c.clm_1a2 end , '',
td =case when val_desc IS null then '' else val_desc end , '',
td =case when c.clm_mchg IS null then '' else c.clm_mchg end
from impact.dbo.clm as c
INNER JOIN impact.dbo.P109A6 as p on p.clm_id1 = c.clm_id1
LEFT JOIN impact.dbo.clme NEGHR ON c.clm_id1 = NEGHR.clme_id and NEGHR.clme_fild = '%NEGHR'
LEFT JOIN impact.dbo.clme NGDNY on c.clm_id1 = NGDNY.clme_id and NGDNY.clme_fild = '%NGDNY'
LEFT JOIN impact.dbo.clme NGAPR on c.clm_id1 = NGAPR.clme_id and NGAPR.clme_fild = '%NGAPR'
join Impact.dbo.vw_valid_302 on val_code = clm_spc
Where
clm_cc1 = 13500 and
clm_stades = 'HOLD' and NGDNY.clme_fild is null and NGAPR.clme_fild is null
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'

Claim Number Patient Last NamePatient First Name Patient Date of Birth Member Last NameMember First Name Date of Service From Date of Service To Provider Office Provider Last Name Provider First Name Provider Specialty Total Charges
' ;

print @tableHTML

EXEC msdb.dbo.sp_send_dbmail @recipients='XXXXX@lth.com',
@query_attachment_filename='Healthnow.XLS',

@subject = 'HealthNow Claims (DIG plan only)',
@body = @tableHTML,
@body_format = 'HTML' ,
@importance = 'HIGH';

END

END


#2

short answer: yes

better answer: Use SSIS for jobs like this


#3

Thanks. I knew someone was going to say this. Since I don't know how to create an SSIS job for this, I have been looking online on how to do this. Looking for good easy video on how to.

I wold need the xls file to be deleted and then created again, so I don't have previous data in the file that get sent out everyday.


#4

I recommend:

Stairway to Integration Services