Proc_BuildTableHtml（根据SQL生成表格形式的html）

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

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

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

IF OBJECT_ID('dbo.Proc_BuildTableHtml') IS NOT NULL
	DROP PROC dbo.Proc_BuildTableHtml
GO
-- =============================================
-- 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字符串
AS
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='<tbody>'+REPLACE(Replace(@tbody,'《','<'),'》','>')+'</tbody>'
	
	--10. 组合两部分
	SET @html='<table cellspacing="0" cellpadding="2" width="100%" border="1" style="border-collapse:collapse;table-layout:fixed;word-wrap:break-word;word-break:break-all;">'
		+@thead + isnull(@tbody,'') + '</table>'
	
	--11. 再次移除临时表
	EXEC (@dropTmpSQL)
END
GO

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

是不是很爽？