SQL将查询内容输出为html表格

CREATE PROCEDURE [dbo].[QUERY_TABLE](@conn varchar(200),@title varchar(200),@desc varchar(max),@query nvarchar(max),@link_ref nvarchar(max),
        @html nvarchar(max) output)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    -- @link_ref: default delimiter is colon.

    if object_id('tempdb..##t') is not null drop table ##t

    declare @exec_query nvarchar(max)
    set @exec_query='select * into ##t from openrowset(''SQLOLEDB'','''+@conn+''',''SET FMTONLY OFF; SET NOCOUNT ON; '+replace(@query,'''','''''')+''') a'
    exec sp_executesql @exec_query
    --select * from ##t
    if exists(select * from ##t)
    begin
    select name as nm,type_name(system_type_id) as tp into #fd from tempdb.sys.all_columns where object_id=object_id('tempdb..##t')
    --select * from #fd
    declare @ref table(OKey varchar(max),OValue varchar(max))
    if len(@link_ref)>0 insert into @ref select distinct * from StringToDic(@link_ref,';',':')
    --select distinct * into #ref from StringToDic(@link_ref,';',':')
    declare @th varchar(max)
    select @th=cast((select [*]=nm from #fd where nm not in (select OValue from @ref) for xml path('th'),type) as nvarchar(max)) 
    declare @h nvarchar(max),@tr nvarchar(max),@chtml nvarchar(max)
    select @h=isnull(@h+',','')+case when tp in ('numeric','datetime','int') then '[td_r]' else '[td]' end+'='+
        case when b.OValue is not null then '''<a href=''''''+'+b.OValue+'+''''''>''+' else '' end+
        case tp when 'numeric' then 'cast(cast(isnull(['+nm+'],0) as decimal(18,2)) as varchar(max))' 
            when 'datetime' then 'isnull(convert(varchar(10),['+nm+'],120),'''')' else 'isnull(['+nm+'],'''')' end +
        case when b.OValue is not null then '+''</a>''' else '' end +',''''' 
    from #fd a left join @ref b on a.nm=b.OKey where a.nm not in (select OValue from @ref)
    select @h='select @chtml=cast((select '+@h+' from ##t for xml path(''tr1'')) as nvarchar(max))'
    --print @h
    exec sp_executesql @h,N'@chtml nvarchar(max) output',@tr output
    
    select @tr=replace(@tr,'<td_r>','<td style="text-align:right">')
    select @tr=replace(@tr,'</td_r>','</td>')
    select @tr=replace(@tr,'<tr1>','<tr style="">')
    select @tr=replace(@tr,'</tr1>','</tr>')
    select @tr=replace(@tr,'&lt;','<')
    select @tr=replace(@tr,'&gt;','>')

    select @html='<h2>'+@title+'</h2><p>'+isnull(@desc,'')+'</p><table rules="all" cellspacing="2" cellpadding="3" border="1"><tr>'+@th+'</tr>'+@tr+'</table>'
    select @html='<div>'+@html+'</div>'
    end
    else
    select @html='<h2>'+@title+'</h2><p>'+isnull(@desc,'')+'</p><p>No record.</p>'

END