将查询输出格式化为html表
将查询输出格式化为html表
提问于 2019-03-17 03:26:57
我需要通过电子邮件发送的sql查询结果是可读的形式。对html的更改将允许在表中创建结果。我需要在以下代码中实现html的帮助。
USE msdb
go
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
IF EXISTS (select Kod, Nazwa from DATABASE.dbo.Towar where Kod NOT LIKE '%?%' and AsId IN (205, 304, 289, 321, 306, 217, 261) and Aktywny = 1)
BEGIN
EXEC sp_send_dbmail @profile_name='PROFILE',
@recipients='myadres@email.com',
@query_result_header=0,
@attach_query_result_as_file=0,
@query="select Kod, Nazwa from DATABASE.dbo.Towar where Kod NOT LIKE '%?%' and AsId IN (205, 304, 289, 321, 306, 217, 261) and Aktywny=1 ORDER BY Kod",
@body_format='text',
@subject='warning';
END;
GO'MgSam‘post Convert a SQL query result table to an HTML table for email中包含的示例实际上解决了转换为html和创建表格的问题。但是，我不知道如何实现“if”条件。
根据MgSam指南-以下代码可以工作-但如果查询不返回数据，则发送电子邮件。
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
DECLARE @html nvarchar(MAX);
EXEC spQueryToHtmlTable @html = @html OUTPUT, @query = "select Kod, Nazwa from DATABASE.dbo.Towar where Kod NOT LIKE '%?%' and AsId IN (205, 304, 289, 321, 306, 217, 261) and Aktywny = 1", @orderBy = N'ORDER BY Kod';
EXEC msdb.dbo.sp_send_dbmail
@profile_name='PROFILE',
@recipients='my@email.com',
@subject = 'WARNING',
@body = @html,
@body_format = 'HTML',
@query_no_truncate = 1,
@attach_query_result_as_file = 0;回答 2
Stack Overflow用户
发布于 2019-03-18 10:56:03
好的，如果SQL Server中的语句非常简单，请看这里：https://docs.microsoft.com/en-us/sql/t-sql/language-elements/if-else-transact-sql
基于您的示例：
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
DECLARE @html nvarchar(MAX);
EXEC spQueryToHtmlTable @html = @html OUTPUT, @query = "select Kod, Nazwa from DATABASE.dbo.Towar where Kod NOT LIKE '%?%' and AsId IN (205, 304, 289, 321, 306, 217, 261) and Aktywny = 1", @orderBy = N'ORDER BY Kod';
IF DATALENGTH(@html) > 0
EXEC msdb.dbo.sp_send_dbmail
@profile_name='PROFILE',
@recipients='my@email.com',
@subject = 'WARNING',
@body = @html,
@body_format = 'HTML',
@query_no_truncate = 1,
@attach_query_result_as_file = 0;
ELSE PRINT 'No results, don't email';Stack Overflow用户
发布于 2019-03-19 13:08:50
如果存在，我使用。有效的代码。
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
DECLARE @html nvarchar(MAX);
IF EXISTS (select Kod, Nazwa from DATABASE.dbo.Towar where Kod NOT LIKE '%?%' and AsId IN (205, 304, 289, 321, 306, 217, 261) and Aktywny = 1)
BEGIN
EXEC spQueryToHtmlTable @html = @html OUTPUT, @query = "select Kod, Nazwa from DATABASE.dbo.Towar where Kod NOT LIKE '%?%' and AsId IN (205, 304, 289, 321, 306, 217, 261) and Aktywny = 1", @orderBy = N'ORDER BY Kod';
EXEC msdb.dbo.sp_send_dbmail
@profile_name='PROFILE',
@recipients='my@email.com',
@subject = 'WARNING',
@body = @html,
@body_format = 'HTML',
@query_no_truncate = 1,
@attach_query_result_as_file = 0;
END;
GO页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/55200677
复制相关文章
相似问题
腾讯云开发者
