把sql sever 查询结果转成 html table

最新推荐文章于 2023-08-14 00:30:28 发布

leecyi

最新推荐文章于 2023-08-14 00:30:28 发布

阅读量5.7k

点赞数

本文链接：https://blog.csdn.net/leecyi/article/details/49660307

版权

-------------------------------------------把查询放入临时表---测试用例--

declare @temptablename varchar(100)
select @temptablename='tempdb..##'+convert(varchar,convert(int, rand()*1000000)) ---防止全局临时表冲突.用rand区别
--select @temptablename
declare @sql varchar(max)
set @sql='select * into '+@temptablename+' from mmWeaveModel '
exec( @sql)

--导出数据为字符串HTML格式.
declare @html varchar(max)

exec pubdb..p_ExportHtmlFromTempobj @temptablename,'hell',@html output,'该邮件由系统自动发送，请不要直接回复'
print @html

set @sql='drop table '+@temptablename
exec( @sql)

-------------------------查询结果转成 html table-------------------------------------

alter PROC [dbo].[p_ExportHtmlFromTempobj]
    @object_name sysname, --表名或视图名
    @title nvarchar(1000) = NULL, -- html 标题, 为NULL时使用@object_name
    @Html nvarchar(max) output,
   @Note varchar(300)=''
AS
set nocount on
---把表里的内容转成html 格式输出.
---lichengyi2015-11-05
declare @tablename varchar(20)
set @tablename=replace(@object_name,'tempdb..','')
--取字段信息
declare @fields table (fieldname varchar(50))
insert into @fields   select c.name
           FROM tempdb.sys.all_columns C
           INNER JOIN tempdb.sys.types T
           ON T.system_type_id = C.system_type_id
           AND T.system_type_id = T.user_type_id
           WHERE C.object_id = OBJECT_ID(@object_name)   order by column_id


--生成标题.
declare @tableTitle varchar(max)
set @tableTitle=''
   SET @tableTitle = @tableTitle +
+cast((select (select [*]= fieldname    from    @fields   FOR XML PATH('th'), TYPE ) for xml path('TR'),type) as nvarchar(max))

   --查询字段
declare @fieldslistsql varchar(max)
set @fieldslistsql=''
~~SET @fieldslistsql = @fieldslistsql +cast((select (select [*]= isnull(fieldname,'')+' as td,'''','    from    @fields   FOR XML PATH(''), TYPE ) for xml path(''),type) as~~

SET @fieldslistsql = @fieldslistsql +cast((select (select [*]=+'isnull('+fieldname+','''') as td,'''','    from    @fields   FOR XML PATH(''), TYPE ) for xml path(''),type) as

nvarchar(max))+''''''

----生成内容.
declare @tdsql nvarchar(max),@TD nvarchar(max)
set @tdsql= +N' select   @tdHTML=cast(( select '+@fieldslistsql +N' from '+@tablename+N'   FOR XML PATH(''tr''), TYPE ) AS NVARCHAR(MAX) )'

exec sp_executesql @tdsql,N'@tdHTML nvarchar(max) output',@TD output

   SET @Html = '<html><meta http-equiv="Content-Type" content="text/html; charset=gb2312">' +char(13)+char(10)+'
               <style type=''text/css''>
               table{border-collapse:collapse;border-spacing:0;border-left:1px solid #888;border-top:1px solid #888;background:#ffefef; font-size: 12px}
               th,td{border-right:1px solid #888;border-bottom:1px solid #888;padding:5px 15px;}
               th{font-weight:bold;background:#ccc;}
               .header {text-align:center;font-weight:bold;white-space:nowrap;color:#7f7e82;}
                .cell_text {vertical-align:top;text-align:left;color:#333333;}
                .cell_num {vertical-align:top;text-align:right;color:#333333;}</style>' +char(13)+char(10)+
               '<body>'+char(13)+char(10)+ N'<H1>'+@title+'</H1>' +   N'<table border="1">' +@tableTitle+char(13)+char(10)+
               @TD+ '</body></html>'
return;