sqlschool.gr logo

articles

Articles of SQLschool.gr Team

Get query results in HTML Table

Antonios Chatzipavlis
Thursday 18 April 2019

Από προσωπική ανάγκη και γιατί δεν είχα χρόνο για να ψάξω να βρω κάτι στο internet έγγραψα την παρακάτω stored procedure που παίρνει ένα SELECT statement και επιστρέφει τα δεδομένα σε html table format.

Η χρήση της είναι εύκολη καθώς παίρνει τρεις παραμέτρους:

  • @sqlquery nvarchar(max) : Είναι το select που θέλουμε να εκτελεστεί.
  • @tablecaption nvarchar(1000) : Είναι το Caption text για το table caption html element με default value 'Query Results'.
  • @debug bit : είναι για την περίπτωση που κάποιος θέλει να κάνει debug την stored procedure και η default value είναι FALSE(0).

Θα πρέπει να επισημανθεί ότι υπάρχουν περιπτώσεις που δεν υποστηρίζονται σε αυτή την stored procedure όπως binary fields , special chars τα οποία δεν υποστηρίζονται στο XML για αυτό το λόγο θα πρέπει το query που θα δοθεί θα πρέπει να έχει φροντίσει να κάνει τα απαραίτητα conversion.

create or alter  proc [dbo].[getQueryResultHTMLTable] (@sqlquery nvarchar(max),@tablecaption nvarchar(1000)='Query Results',@debug bit = 0)
with recompile
as
begin
    
    declare @tsql nvarchar (max);
    declare @columnslist nvarchar (max) = '';
    declare @columnslist_header nvarchar (max) = '';

    set nocount on;
 
    begin try

    -- create temp table

         IF OBJECT_ID('tempdb..#T', 'U') IS NOT NULL
        begin
           DROP TABLE #T;
        end
        else
        begin
            create table #t ( temp_col int );
        end;
        
        if ( @debug = 1 )
        begin
            select * from #t;
        end;

        -- find  columns and data type for alter table

        select @columnslist += quotename(name) + ' ' + system_type_name  + ' , '
        from sys.dm_exec_describe_first_result_set(@sqlquery, null, 0)    
        where is_hidden = 0;
        
        set @columnslist = left (@columnslist, len (@columnslist) - 1);
        
        if ( @debug = 1 ) 
        begin
            select @columnslist;
        end;
        
        -- create temp table 

        set @tsql = 'alter table #t add ' + @columnslist;
        
        if @debug=1
        begin
            print @tsql;
        end;

        exec (@tsql);
        
        alter table #t drop column temp_col;
        
        if @debug=1
        begin
            select 'alter' as 'alter', * from #t;
        end;
        
        -- insert data to temp table
    
        insert into #t
        exec (@sqlquery);

        if ( @debug = 1 ) 
        begin
            select * 
            from sys.dm_exec_describe_first_result_set('select * from #t', null, 0);
        end;
        
        -- find columns for select 

        set @columnslist='';
        
        select @columnslist += quotename(name) +' as td , ' 
        from sys.dm_exec_describe_first_result_set('select * from #t', null, 0)
        where is_hidden = 0;
        
        set @columnslist = left (@columnslist, len (@columnslist) - 1);
        
        if ( @debug = 1 ) 
        begin
            select @columnslist;
        end;
        
        -- find columns for table header

        set @columnslist_header='';
        
        select @columnslist_header += ''''+name + ''' as th,'
        from sys.dm_exec_describe_first_result_set('select * from #t', null, 0)
        where is_hidden = 0;
        
        set @columnslist_header = left (@columnslist_header, len (@columnslist_header) - 1);
        
        if ( @debug = 1 ) 
        begin
            select @columnslist_header;
        end;
        
        -- produce html table

        set @tsql = 'SELECT 
                    (SELECT '''+@tablecaption +''' FOR XML PATH(''''),TYPE) AS ''caption''
                    ,(SELECT ' +  @columnslist_header + ' FOR XML raw(''tr''),ELEMENTS, TYPE) AS ''thead''
                    ,(SELECT ' + @columnslist + ' from #t FOR XML RAW(''tr''), ELEMENTS,  TYPE ) AS ''tbody''
                    FOR XML PATH(''''), ROOT(''table'')';
        
        if ( @debug = 1 ) 
        begin
            print @tsql;
        end;

        exec (@tsql);

        -- drop temp table
    
        if object_id('tempdb..#t', 'u') is not null
        begin
            drop table #t;
        end;

   end try
   begin catch
        if object_id('tempdb..#t', 'u') is not null
        begin
            drop table #t;
        end;
        throw;
   end catch
   
end
go

//antonch

Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios Chatzipavlis is a highly experienced Data Solutions Consultant and Trainer. He has been working in the IT industry since 1988, holding various roles such as senior developer, IT Manager, Data & AI Solutions Architect and Consultant.

Since 1995, Antonios has focused on modern technologies and software development tools, primarily by Microsoft. He has specialized in Data & AI since 2000, with expertise in Microsoft Data Platform (SQL Server, Azure SQL Databases, Azure Synapse Analytics, Microsoft Fabric, Power BI, AI) and Databricks.

Antonios is also a Microsoft Certified Trainer (MCT) for over 25 years, has been recognized as a Microsoft Most Valuable Professional (MVP) in Data Platform since 2010 and he is in the Data Expert 40 Powerlist 2024 by Boussias. He is the co-founder and visionary behind XLYTiCA, a company dedicated to Data & AI solutions.

Episode

Task Flows in Microsoft Fabric

image

More Episodes...

Tip

Get Certified: Become a Fabric Data Engineer

More Tips...

Become a member

If you want to receive updates from us become a member to our community.

Connect

Explore

Learn

sqlschool.gr © 2010-2025 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.