Sends an email when results are returned from a query

SendDBEmailwithTabularQuery.sql

-- =============================================
-- 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