Introduction
If you need a udp for making an HTML table (ex notification for mail message), here there is my solution without CLR.
You can override the parameters or leave the default style.
Procedure
Here is the code of user defined procedure:
ALTER PROCEDURE [dbo].[udp_QueryToHtmlTable]
@SelectStatment NVARCHAR(MAX)
,@SortingCondition NVARCHAR(MAX) =null
,@RowLimit INT = NULL
,@TableStyle VARCHAR(MAX) =NULL
,@HeaderStyle VARCHAR(MAX) =NULL
,@RowStyle VARCHAR(MAX) =NULL
,@OddRowStyle VARCHAR(MAX) =NULL
,@MessageForTopRow NVARCHAR(MAX)=null
,@HtmlResult VARCHAR(MAX) OUTPUT
AS
BEGIN
DECLARE @TotalRow INT
DECLARE @TSQL NVARCHAR(MAX)
DECLARE @HtmlLimitMessage VARCHAR(MAX)
DECLARE @HeaderColumnList VARCHAR(MAX)
SET @HtmlLimitMessage=''
IF isNULL(@RowLimit,'0')<=0 SET @RowLimit=10
IF isNULL(@TableStyle,'')='' _
SET @TableStyle='font-family: ''''Helvetica Neue'''', _
Helvetica, Arial; font-size: 14px; line-height: 20px; font-weight: 400; _
color: #3b3b3b; -webkit-font-smoothing: antialiased; font-smoothing: antialiased;margin: 0 0 40px 0; _
width: 100 %; box-shadow: 0 1px 3px rgba(0,0,0,0.2);border-collapse: collapse;'
IF isNULL(@HeaderStyle,'')='' _
SET @HeaderStyle='font-weight: 900; color: #ffffff; background: #434a59;'
IF isNULL(@RowStyle,'')='' SET @RowStyle='background: #f6f6f6;'
IF @OddRowStyle is NULL SET @OddRowStyle= 'background: #e9e9e9;'
IF isNULL(@MessageForTopRow,'')='' _
SET @MessageForTopRow= 'Here the top $ROWLIMIT$ of $TOTALROW$'
SET @TSQL=N' set @Count= (SELECT count(1) from ( ' + @SelectStatment + ' ) AS t ) '
EXEC sp_executesql @TSQL,N'@Count int out' , @Count=@TotalRow OUT
IF (@TotalRow> @RowLimit)
BEGIN
SET @HtmlLimitMessage ='<p>' +replace( replace_
( @MessageForTopRow,'$ROWLIMIT$', @RowLimit),'$TOTALROW$', @TotalRow) + '</p>'
END
BEGIN TRY
DROP TABLE #htmlTempTable
END TRY
BEGIN CATCH END CATCH
SET @TSQL ='
SELECT TOP(1) *
INTO #HtmlTempTable
FROM ( ' + @SelectStatment + ' ) AS t
SET @HeaderColumnList =cASt((SELECT Name AS [td]
FROM tempdb.sys.columns
WHERE object_id=OBJECT_ID(''tempdb.dbo.#HtmlTempTable'')
ORDER BY column_id ASC FOR XML PATH('''')) AS VARCHAR(MAX))
SET @HtmlResult =''<table' + (CASE WHEN @TableStyle='' _
THEN '' ELSE ' style="' + @TableStyle + '"' END) + '>''
SET @HtmlResult = @HtmlResult + ''<tr' + _
(CASE WHEN @HeaderStyle='' THEN '' ELSE ' style="' + _
@HeaderStyle + '"' END) + '>''
SET @HtmlResult = @HtmlResult + @HeaderColumnList + ''</tr>''
'
exec sp_executesql @TSQL ,N'@HtmlResult varchar(MAX) OUT,@HeaderColumnList varchar(MAX) OUT' , _
@HtmlResult=@HtmlResult OUT , @HeaderColumnList=@HeaderColumnList OUT
SET @TSQL ='
DECLARE @index INT
DECLARE @limit INT
SELECT TOP( ' + cast( @RowLimit AS varchar) + ' ) * ,
ROW_NUMBER() OVER(ORDER BY ' + (CASE WHEN isNULL(@SortingCondition,'')=''
THEN '[' +replace(replace( replace_
(@HeaderColumnList,'</td><td>','],['),_
'<td>',''),'</td>','') +']'
ELSE @SortingCondition END )
+ ') AS [$rn]
INTO #HtmlTempTable
FROM ( ' + @SelectStatment + ' ) AS t
ALTER TABLE #HtmlTempTable
ADD [$HtmlResult] VARCHAR(MAX)
SET @limit = (SELECT MAX( [$rn]) FROM #HtmlTempTable )
SET @index =1
WHILE @index<=@limit
BEGIN
IF @index%2=0
BEGIN
SET @HtmlResult=@HtmlResult + ''<tr' + (CASE WHEN @RowStyle + _
@OddRowStyle='' THEN '' ELSE ' style="' + _
@RowStyle + @OddRowStyle + '"' END) + '>''
END
ELSE
BEGIN
SET @HtmlResult=@HtmlResult + ''<tr' + _
(CASE WHEN @RowStyle='' THEN '' ELSE ' style="' + _
@RowStyle + '"' END) + '>''
END
SET @HtmlResult=@HtmlResult+ cASt((SELECT isNULL([' + replace(replace_
(replace(@HeaderColumnList,'</td><td>','],'''') _
AS [td],isNULL(['),'<td>',''),'</td>','') + '],'''') AS [td]
FROM #HtmlTempTable
WHERE [$rn]=@index
FOR XML RAW(''''),ELEMENTS,TYPE, BINARY BASE64 ) _
AS VARCHAR(MAX)) + ''</tr>''
SET @index+=1
END
SET @HtmlResult=@HtmlResult+ ''</table>''
'
exec sp_executesql @TSQL ,N'@HtmlResult VARCHAR(MAX) OUT ' , @HtmlResult=@HtmlResult OUT
set @HtmlResult = @HtmlLimitMessage + @HtmlResult
print @HtmlResult
END
How to Run
And for running it, you can:
DECLARE @HtmlTable varchar(MAX)
exec udp_QueryToHtmlTable @SELECTStatment='SELECT Employeeid,lastname,_
firstName,homephone,title from [NORTHWND].[dbo].[Employees] '
,@SortingCondition ='Title desc'
,@rowlimit=5
,@HtmlResult= @HtmlTable output
SELECT @HtmlTable
Automatically, the procedure takes the top 10 rows and if the query contains more rows, then @rowlimit
parameters will set a paragraph before the table.
Output Sample
Here are the top 5 of 9:
Employeeid | lastname | firstName | homephone | title |
2 | Fuller | Andrew | (206) 555-9482 | Vice President, Sales |
3 | Leverling | Janet | (206) 555-3412 | Sales Representative |
4 | Peacock | Margaret | (206) 555-8122 | Sales Representative |
6 | Suyama | Michael | (71) 555-7773 | Sales Representative |
7 | King | Robert | (71) 555-5598 | Sales Representative |