Click here to Skip to main content
14,027,781 members
Click here to Skip to main content
Add your own
alternative version

Stats

6.5K views
8 bookmarked
Posted 30 Jan 2018
Licenced CPOL

Select Statement to HTML Table

, 2 Feb 2018
Rate this:
Please Sign up or sign in to vote.
User defined procedure for make an HTML table from T-SQL Select statment

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Luca Astolfi
United States United States
No Biography provided

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 3 Pin
Jim_Snyder5-Feb-18 7:50
professionalJim_Snyder5-Feb-18 7:50 
SuggestionWhy? Pin
fmsalmeida30-Jan-18 7:12
professionalfmsalmeida30-Jan-18 7:12 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web04 | 2.8.190419.4 | Last Updated 2 Feb 2018
Article Copyright 2018 by Luca Astolfi
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid