Select Statement to HTML Table

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: