Skip to content

Instantly share code, notes, and snippets.

@bh3605
Created April 28, 2021 12:54
Show Gist options
  • Save bh3605/676e47cd0e9325a9adc57d49c54b2117 to your computer and use it in GitHub Desktop.
Save bh3605/676e47cd0e9325a9adc57d49c54b2117 to your computer and use it in GitHub Desktop.
Sends an email when results are returned from a query
-- =============================================
-- Author: Hair, Bryson
-- Create date: 4/27/2021
-- Description: This stored procedure will send an email from the database with the query results as an html table in the email.
-- =============================================
ALTER PROCEDURE [dbo].[CFT_SendDBEmailwithTabularQuery]
(
@qSELECT NVARCHAR(100), --The select part of the sql statement, which can include top X
@fieldlist NVARCHAR(MAX), --Pipe delimited list of fields, which can include aliases
@qFROM NVARCHAR(MAX), --The from part of the sql statment, which can include joins
@qWHERE NVARCHAR(MAX) = '', --The where part of the sql statement
@qGroupBy NVARCHAR(MAX) = '',--The group by clause
@qHaving NVARCHAR(MAX) = '',--The having clause
@qOrderBy NVARCHAR(MAX) = '', --The order by part of the sql statement
@recipients NVARCHAR(4000), --The recipients of the email
@subject NVARCHAR(400), --The subject of the email
@Title NVARCHAR(4000) = '' --The title of the html table that holds the query results
)
AS
BEGIN
--Declare initial variable.
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)
DECLARE @resultexist NVARCHAR(MAX)
DECLARE @tblfieldheader NVARCHAR(MAX) = ''
DECLARE @tempfield NVARCHAR(MAX) = ''
CREATE TABLE #Fields (ID INT IDENTITY(1,1),field NVARCHAR(MAX))
DECLARE @i INT = 1, @j INT = 1, @SendEmail INT
DECLARE @splitcnt INT
DECLARE @fieldcount INT
--STRING_SPLIT section
CREATE TABLE #splitTable (ID INT IDENTITY(1,1), Value nvarchar(MAX))
DECLARE @position int
DECLARE @separator NVARCHAR(1) = '|'
SET @position = 1
SET @fieldlist = @fieldlist + @separator
WHILE charindex(@separator,@fieldlist,@position) <> 0
BEGIN
INSERT into #splitTable
SELECT substring(@fieldlist, @position, charindex(@separator,@fieldlist,@position) - @position)
SET @position = charindex(@separator,@fieldlist,@position) + 1
END
--STRING_SPLIT section
--Find the number of fields in the query
SELECT @splitcnt = LEN(@fieldlist)-LEN(REPLACE(@fieldlist,'|',''))
--Loop through the fields and put each on into the #Fields temp table as a new record
WHILE @j <= @splitcnt
BEGIN
INSERT INTO #Fields ( field ) SELECT value FROM #splitTable WHERE ID = @j
SET @j += 1
END
DROP TABLE #splitTable
SELECT @fieldcount = @splitcnt --flawed this code is flawed ---> --@splitcnt + 1 --Will be the splitcnt + 1, otherwise MAX(ID) FROM #Fields
--Start setting up the sql statement for the query.
SET @sql = @qSELECT
--Loop through the #Fields table to get the field list
WHILE @i <= @fieldcount
BEGIN
SELECT @tempfield = field FROM #Fields WHERE ID = @i
--------------------------------------------------------------------------------------------------------------------------------------------------------------
--This next section is required in case a field is aliased. For the xml, we need to get rid of the aliases, the table header will only require the aliases.
--NULL values need to be shown as a string = 'NULL' or the html table will just skip the cell and all values after that in the row will be shifted left.
---------------------------------------------------------------------------------------------------------------------------------------------------------------
IF RIGHT(@tempfield,1) = ']' OR CHARINDEX(' as ',@tempfield) = 0
BEGIN
--Set the xml field to be the entire field name
SET @sql = @sql + ' ISNULL(CAST(' + @tempfield + ' AS NVARCHAR(4000)),''NULL'') AS ''td'','
--Set the table header field to be the entire field name
SET @tblfieldheader = @tblfieldheader + '<th>' + @tempfield + '</th>'
END
ELSE
BEGIN
--Set the xml field to be the field name minus the alias
SET @sql = @sql + ' ISNULL(CAST(' + LEFT(@tempfield,LEN(@tempfield) - (CHARINDEX(' sa ',REVERSE(@tempfield))+3)) + ' AS NVARCHAR(4000)),''NULL'') AS ''td'','
--Set the table header field to be the field name's alias
SET @tblfieldheader = @tblfieldheader + '<th>' + RIGHT(@tempfield,CHARINDEX(' sa ',REVERSE(@tempfield))-1) + '</th>'
END
--Increment the counter.
SET @i += 1
END
--Trim the extra four characters of the end of @sql.
SET @sql = LEFT(@sql, LEN(@sql)-1)
--Add the from, where, group by, having, and order by clause to the select statement.
SET @sql = @sql + ' ' + @qFROM + ' ' + @qWHERE + ' ' + @qGroupBy + ' ' + @qHaving + ' ' + @qOrderBy
select @sql;
--Put the set xml command around the sql statement.
--original
--SET @sql = 'SET @XML = CAST(( ' + @sql + ' FOR XML PATH(''tr''),ELEMENTS ) AS NVARCHAR(MAX))'
SET @sql = 'SELECT @XML = (SELECT CAST((SELECT FOO.* FROM (' + @sql + ') AS FOO FOR XML PATH(''tr''), ELEMENTS) AS NVARCHAR(MAX)))'
select @sql
/*select cast((select * from CFT_Duplicate_Claim_Acknowledgements union all select * from CFT_Duplicate_Claim_Acknowledgements)) f for xml path('tr')
select (
cast(
(select foo.* from (
select * from CFT_Duplicate_Claim_Acknowledgements union all select * from CFT_Duplicate_Claim_Acknowledgements
) foo
for xml path('tr')) as nvarchar(max))
)*/
--Run the sql that will create the xml.
EXEC sp_executesql @sql, N'@xml nvarchar(max) output', @xml OUTPUT
--Create the body of the email, which contains the xml results of the query.
SET @body = '<html><body><H3>' + @Title + '</H3><table border = 1><tr>' + @tblfieldheader + '</tr>' + @xml + '</table></body></html>'
/*
DECLARE @xml NVARCHAR(MAX)
declare @foo as nvarchar(max)
declare @jjj nvarchar(max) = 'select * from (select a1.dcn from cft_duplicate_claim_acknowledgements a1 group by a1.dcn union all select a2.dcn from cft_duplicate_claim_acknowledgements a2 group by a2.dcn) h2'
--set @jjj = 'SELECT @XML = (SELECT CAST((SELECT * FROM (SELECT ISNULL(CAST(H2.DCN AS NVARCHAR(4000)),''NULL'') AS ''td'','''', ISNULL(CAST( H2.RECEIVED_DATE AS NVARCHAR(4000)),''NULL'') AS ''td'','''', ISNULL(CAST(STUFF(( SELECT '', '' + D3.CPT_PROC_CODE FROM CFT_WCBP_CMS_DETAIL_TBL D3 WHERE (D2.DCN = D3.DCN) GROUP BY D3.CPT_PROC_CODE FOR XML PATH(''''),TYPE).value(''(./text())[1]'',''VARCHAR(MAX)'') ,1,2,'''') AS NVARCHAR(4000)),''NULL'') AS ''td'','''', ISNULL(CAST( D2.DOS_FROM AS NVARCHAR(4000)),''NULL'') AS ''td'','''', ISNULL(CAST( D2.DOS_TO AS NVARCHAR(4000)),''NULL'') AS ''td'','''', ISNULL(CAST( CheckData2.AMOUNT AS NVARCHAR(4000)),''NULL'') AS ''td'','''', ISNULL(CAST( MAX(Stat2.STATUS_DESC) AS NVARCHAR(4000)),''NULL'') AS ''td'','''', ISNULL(CAST( ISNULL(H2.BILLING_PHYS_NAME, ''Name not Found'') + ''<br />'' + H2.FEDERAL_TAX_ID AS NVARCHAR(4000)),''NULL'') AS ''td'','''', ISNULL(CAST( TCT.CODE_DESC AS NVARCHAR(4000)),''NULL'') AS ''td'' FROM dbo.CFT_WCBP_CMS_HEADER_TBL H WITH(NOLOCK) INNER JOIN dbo.CFT_WCBP_CMS_DETAIL_TBL D WITH(NOLOCK) ON D.DCN = H.DCN AND H.DCN = ''20258W255018'' AND D.CPT_PROC_CODE <> ''CONADJ'' INNER JOIN RM_Util.dbo.CFT_DuplicateReview CheckData WITH(NOLOCK) ON H.DCN = CheckData.DCN AND H.FEDERAL_TAX_ID = CheckData.PAYEE_TAX_ID LEFT JOIN dbo.CFT_DOCUMENT_STATUS_HISTORY StatusHist WITH(NOLOCK) ON StatusHist.DCN = CheckData.DCN LEFT JOIN dbo.CFT_STATUSES Stat WITH(NOLOCK) ON Stat.STATUS_ID = StatusHist.STATUS_ID AND Stat.STATUS_DESC LIKE ''%REPRICE%'' INNER JOIN dbo.CFT_WCBP_CMS_HEADER_TBL H2 WITH(NOLOCK) ON H2.CLAIM_NUMBER = H.CLAIM_NUMBER AND H2.DCN <> H.DCN INNER JOIN dbo.CFT_WCBP_CMS_DETAIL_TBL D2 WITH(NOLOCK) ON H2.DCN = D2.DCN AND D.CPT_PROC_CODE = D2.CPT_PROC_CODE AND D.DOS_FROM = D2.DOS_FROM INNER JOIN RM_Util.dbo.CFT_DuplicateReview CheckData2 WITH(NOLOCK) ON H2.DCN = CheckData2.DCN AND H2.FEDERAL_TAX_ID = CheckData2.PAYEE_TAX_ID LEFT JOIN dbo.CFT_DOCUMENT_STATUS_HISTORY StatusHist2 WITH(NOLOCK) ON StatusHist2.DCN = CheckData2.DCN LEFT JOIN dbo.CFT_STATUSES Stat2 WITH(NOLOCK) ON Stat2.STATUS_ID = StatusHist2.STATUS_ID AND Stat2.STATUS_DESC LIKE ''%REPRICE%'' LEFT JOIN CFT_DOCUMENT_STATUS DS ON H.DCN = DS.DCN LEFT JOIN rm1.dbo.CODES_TEXT TCT ON DS.TRANSACTION_CODE = TCT.CODE_ID GROUP BY H.CLAIM_NUMBER , H.RECEIVED_DATE , H.DCN , D.DOS_FROM , H.BILLING_NPI , CheckData.AMOUNT , H2.DCN , H2.RECEIVED_DATE , D2.DOS_FROM , D2.DCN , H2.BILLING_NON_NPI , CheckData2.AMOUNT , H2.FEDERAL_TAX_ID , H2.BILLING_PHYS_NAME , D2.DOS_TO , TCT.CODE_DESC UNION ALL SELECT H2.DCN , H2.RECEIVED_DATE ,STUFF(( SELECT '', '' + D.REV_CD FROM dbo.CFT_WCBP_UB04_DETAIL_TBL D WHERE (H.DCN = D.DCN) GROUP BY D.REV_CD FOR XML PATH(''''),TYPE).value(''(./text())[1]'',''VARCHAR(MAX)'') ,1,2,'''') AS CPT_CODE , D2.SERV_DATE AS DOS_FROM , NULL AS DOS_TO , CheckData2.AMOUNT , MAX(Stat2.STATUS_DESC) Repriced , ISNULL(H2.PROVIDER_NAME, ''Name not Found'') + ''<br />'' + H2.FED_TAX_NO AS PROVIDER , TCT.CODE_DESC AS TransactionCode FROM dbo.CFT_WCBP_UB04_HEADER_TBL H WITH(NOLOCK) INNER JOIN dbo.CFT_WCBP_UB04_DETAIL_TBL D WITH(NOLOCK) ON D.DCN = H.DCN AND H.DCN = ''20258W255018'' AND D.REV_CD <> ''CONADJ'' INNER JOIN RM_Util.dbo.CFT_DuplicateReview CheckData WITH(NOLOCK) ON H.DCN = CheckData.DCN AND H.FED_TAX_NO = CheckData.PAYEE_TAX_ID LEFT JOIN dbo.CFT_DOCUMENT_STATUS_HISTORY StatusHist WITH(NOLOCK) ON StatusHist.DCN = CheckData.DCN LEFT JOIN dbo.CFT_STATUSES Stat WITH(NOLOCK) ON Stat.STATUS_ID = StatusHist.STATUS_ID AND Stat.STATUS_DESC LIKE ''%REPRICE%'' INNER JOIN dbo.CFT_WCBP_UB04_HEADER_TBL H2 WITH(NOLOCK) ON H2.CLAIM_NUMBER = H.CLAIM_NUMBER AND H2.DCN <> H.DCN INNER JOIN dbo.CFT_WCBP_UB04_DETAIL_TBL D2 WITH(NOLOCK) ON H2.DCN = D2.DCN AND D.REV_CD = D2.REV_CD AND D.SERV_DATE = D2.SERV_DATE INNER JOIN RM_Util.dbo.CFT_DuplicateReview CheckData2 WITH(NOLOCK) ON H2.DCN = CheckData2.DCN AND H2.FED_TAX_NO = CheckData2.PAYEE_TAX_ID LEFT JOIN dbo.CFT_DOCUMENT_STATUS_HISTORY StatusHist2 WITH(NOLOCK) ON StatusHist2.DCN = CheckData2.DCN LEFT JOIN dbo.CFT_STATUSES Stat2 WITH(NOLOCK) ON Stat2.STATUS_ID = StatusHist2.STATUS_ID AND Stat2.STATUS_DESC LIKE ''%REPRICE%'' LEFT JOIN CFT_DOCUMENT_STATUS DS ON H.DCN = DS.DCN LEFT JOIN rm1.dbo.CODES_TEXT TCT ON DS.TRANSACTION_CODE = TCT.CODE_ID GROUP BY H.CLAIM_NUMBER , H.RECEIVED_DATE , H.DCN , D.SERV_DATE , H.NPI , CheckData.AMOUNT , H2.DCN , H2.RECEIVED_DATE , D2.SERV_DATE , D2.DCN , H2.NPI , CheckData2.AMOUNT , H2.FED_TAX_NO , TCT.CODE_DESC , H2.PROVIDER_NAME ) h1 FOR XML PATH(''tr''), ELEMENTS) AS NVARCHAR(MAX)))'
--set @jjj = 'select @xml = (select cast((' + @jjj + ' for xml path(''tr''), elements) as nvarchar(max)))'
EXEC sp_executesql @jjj, N'@xml nvarchar(max) output', @xml OUTPUT
select @xml
--select @foo = RelatedDCNs from cft_duplicate_claim_acknowledgements
select * from (select * from cft_duplicate_claim_acknowledgements union all select * from cft_duplicate_claim_acknowledgements) as foo
*/
--Drop the fields temp table.
DROP TABLE #Fields
--Set the variable that will be tested to verify there was at least one result.
SET @resultexist = 'IF NOT EXISTS(SELECT TOP 1 1 ' + @qFROM + ' ' + @qWHERE + ' ' + @qGroupBy + ' ' + @qHaving + ') SET @SendEmail = 0 ELSE SET @SendEmail = 1'
SELECT @resultexist
--Capture whether or not any rows were returned
exec sp_executesql @resultexist, N'@SendEmail int output', @SendEmail OUTPUT
--Check the variable.
IF @SendEmail = 1
BEGIN
--If rows were returned, send the email.
EXEC msdb.dbo.sp_send_dbmail
@recipients = @recipients,
@subject = @subject,
@body = @body,
@body_format = 'HTML';
END
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment