sql 查出表转换为html,如何根据SQL生成表格形式的html-CSDN博客

用SQL Server发邮件是非常方便的，大家都喜欢用，但用文本表达数据并不友好。

html是美观的，但将数据构建成表格形式的html并不容易。

为一劳永逸，写了这个存储过程，造福自己也造福别人。

IF OBJECT_ID('dbo.Proc_BuildTableHtml') IS NOT NULL

DROP PROC dbo.Proc_BuildTableHtml

-- =============================================

-- Author:yng

-- Create date: 2016-09-27

-- Description:根据sql构建表格html字符串

-- Example:

DECLARE @html NVARCHAR(MAX)

EXEC Proc_BuildTableHtml 'select top 2 object_id,[name] into tempdb..tmpHtml from master.sys.tables',1,@html OUT

SELECT @html

-- =============================================

CREATE PROCEDURE dbo.Proc_BuildTableHtml

@sql NVARCHAR(MAX)='select top 2 object_id,[name] into tempdb..tmpHtml from sys.tables'--获取数据的SQL, 必须包含将数据插入到 tempdb..xxx 普通表(临时表不可以)

,@addSeq BIT=1--是否添加序号列

,@html NVARCHAR(MAX) OUT--输出参数：html字符串

BEGIN

SET NOCOUNT ON;

--1. 获取 SQL 中的 temdb 的表名

DECLARE @tempTableName NVARCHAR(100),@tempSQL NVARCHAR(MAX), @dropTmpSQL NVARCHAR(MAX)

SET @tempSQL=SUBSTRING(@sql,CHARINDEX('tempdb',@sql),LEN(@sql))

SET @tempTableName=SUBSTRING(@tempSQL,0,CHARINDEX(' ',@tempSQL))

--2.先移除 tempdb 的普通表

SET @dropTmpSQL='

IF OBJECT_ID('''+@tempTableName+''') IS NOT NULL

BEGIN

drop table '+@tempTableName+'

END

EXEC (@dropTmpSQL)

--3. 执行sql，将数据存到 tempdb 中的普通表

EXEC (@sql)

--4. 将列信息插入到表变量

DECLARE @columnsTab TABLE (rid INT IDENTITY(1,1) PRIMARY KEY, columnName NVARCHAR(100), aliasName NVARCHAR(100), is_identity BIT,column_id INT)

INSERT INTO @columnsTab (columnName, aliasName, is_identity,column_id)

SELECT CASE when c.is_identity=1 THEN c.name+'_2' ELSE c.name END, c.name , c.is_identity, c.column_id

FROM tempdb.sys.[columns] AS c WHERE c.[object_id]=OBJECT_ID(@tempTableName)

UNION ALL

SELECT 'ridHtml','序号',1,0

--5. 如果有标识列，要增加新列，新列值=原标识列，再去掉原标识列

DECLARE @identityColumnName VARCHAR(100),@identityColumnName2 VARCHAR(100)

SELECT @identityColumnName=c.name FROM tempdb.sys.[columns] AS c WHERE c.[object_id]=OBJECT_ID(@tempTableName) AND c.is_identity=1

IF @identityColumnName IS NOT NULL

BEGIN

SET @identityColumnName2=@identityColumnName+'_2'

SET @tempSQL='alter table '+@tempTableName+' add '+@identityColumnName2+' bigint'

EXEC (@tempSQL)

SET @tempSQL='update '+@tempTableName+' Set '+@identityColumnName2+'='+@identityColumnName

EXEC (@tempSQL)

SET @tempSQL='alter table '+@tempTableName+' drop column '+@identityColumnName

EXEC (@tempSQL)

END

--6. 添加序号列(一是序号列可用上，二是奇偶行变色也能用上)

SET @tempSQL='alter table '+@tempTableName+' add ridHtml int identity(1,1)'

EXEC (@tempSQL)

--7. 获取表头部分的 html => thead

DECLARE @thead NVARCHAR(MAX),@columnsGetData NVARCHAR(MAX),@sqlGetBody NVARCHAR(MAX),@tbody NVARCHAR(MAX)

SELECT @thead = '《thead》《tr style="background:#f3f9ff;"》'+ ( SELECT '《th》'+c.aliasName+'《/th》' FROM @columnsTab c

WHERE @addSeq=1 OR c.columnName!='ridHtml'

ORDER BY c.column_id

FOR XML PATH('') ) +'《/tr》《/thead》'

SET @thead=REPLACE(Replace(@thead,'《','')

--8. 构建获取列数据的sql

SELECT @columnsGetData =

'''《tr style="background-color: ''+CASE WHEN ridHtml%2=0 THEN ''rgb(255, 248, 220);"'' ELSE ''rgb(255, 255, 255);"'' END + ''》'''+

(SELECT '''《td》''+ CASE WHEN '

+c.columnName+' IS NULL THEN ''NULL'' ELSE CAST('

+c.columnName+' AS NVARCHAR(MAX)) END+''《/td》'''

FROM @columnsTab c

WHERE @addSeq=1 OR c.columnName!='ridHtml'

ORDER BY c.column_id

FOR XML PATH(''))

+'''《/tr》'''

SET @columnsGetData=replace(REPLACE(@columnsGetData,'》''《','》《'),'》''''《','》《')

--9. 获取数据部分的 html => tbody

SET @sqlGetBody='select @tbody=(SELECT '+@columnsGetData+' FROM '+@tempTableName+' for xml path(''''))'

EXEC　sp_executesql @sqlGetBody,N'@tbody nvarchar(max) out',@tbody out

SET @tbody='

'+REPLACE(Replace(@tbody,'《','')+''

--10. 组合两部分

SET @html='

+@thead + isnull(@tbody,'') + '

--11. 再次移除临时表

EXEC (@dropTmpSQL)

END

将Example中获取的字符串粘贴到记事本，再另存为 1.html ，用chrome 打开，效果如下：

是不是很爽？