将SQL查询结果表转换为电子邮件的HTML表格 [英] Convert a SQL query result table to an HTML table for email
问题描述
我正在运行一个返回结果表的SQL查询。我希望使用dbo.sp_send_dbMail在电子邮件中发送表。
在SQL中有一种简单的方法将表转换为HTML表格吗?目前,我正在使用COALESCE手动构建它,并将结果放入一个varchar,我将其用作emailBody。
有没有更好的方法来做到这一点?
是一种可以从将查询输出格式化为HTML表格 - 简单的方法。你需要用你自己的查询的细节替换这个例子中的查询,它会得到一个表格和一个行数列表。
<$ c (
选择td = dbtable +'< / td>< td>'+ cast(实体为varchar(30))+'< / td>< td>'cast(rows as varchar(30))
from(
select dbtable = object_name(object_id),
entities = count(distinct name),
rows = count(*)
from sys.columns
group by object_name(object_id)
)as d
for xml path 'tr'),type)as varchar(max))
set @body ='< table cellpadding =2cellspacing =2border =1>'
+'< tr>第>数据库表第th th实体计数第r行总行< th>'
+替换(<替换(@body,'& lt;','<'),'& gt;','> ')
+'< / table>'
print @body
一旦您拥有 @body
,您就可以使用任何您想要的电子邮件机制。
I am running a SQL query that returns a table of results. I want to send the table in an email using dbo.sp_send_dbMail.
Is there a straightforward way within SQL to turn a table into an HTML table? Currently, I'm manually constructing it using COALESCE and putting the results into a varchar that I use as the emailBody.
Is there a better way to do this?
Here is one way to do it from an article titled "Format query output into an HTML table - the easy way". You would need to substitute the details of your own query for the ones in this example, which gets a list of tables and a row count.
declare @body varchar(max)
set @body = cast( (
select td = dbtable + '</td><td>' + cast( entities as varchar(30) ) + '</td><td>' + cast( rows as varchar(30) )
from (
select dbtable = object_name( object_id ),
entities = count( distinct name ),
rows = count( * )
from sys.columns
group by object_name( object_id )
) as d
for xml path( 'tr' ), type ) as varchar(max) )
set @body = '<table cellpadding="2" cellspacing="2" border="1">'
+ '<tr><th>Database Table</th><th>Entity Count</th><th>Total Rows</th></tr>'
+ replace( replace( @body, '<', '<' ), '>', '>' )
+ '</table>'
print @body
Once you have @body
, you can then use whatever email mechanism you want.
这篇关于将SQL查询结果表转换为电子邮件的HTML表格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!