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'
| Claim Number | Patient Last Name | ' +Patient First Name | Patient Date of Birth | Member Last Name | ' +Member 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