go backarticles

Articles of SQLschool.gr Team

Get query results in HTML Table

Antonios Chatzipavlis

Από προσωπική ανάγκη και γιατί δεν είχα χρόνο για να ψάξω να βρω κάτι στο 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


Relative Articles

Leave your comment

Login with your SQLschool.gr account if you want to comment on this article.


Follow us in
PASS chapter logo

The Official PASS Local Group for Greece

sql school greece logo
© 2010-2019 All rights reserved