格式查询输出到HTML表中

Question

我需要电子邮件发送的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='[e-mail]',
        @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"帖子中包含的示例将SQL查询结果表转换为HTML表的电子邮件，实际上解决了转换为HTML并创建表的问题。但是，我不知道如何实现"如果"条件。

根据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='[e-mail]',
    @subject = 'WARNING',
    @body = @html,
    @body_format = 'HTML',
    @query_no_truncate = 1,
    @attach_query_result_as_file = 0;

Answer 1

好吧，如果SQL Server中的语句非常简单，请在此处查看：https://learn.microsoft.com/en-us/sql/t-sql/language-elements/if-else-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='[e-mail]',
    @subject = 'WARNING',
    @body = @html,
    @body_format = 'HTML',
    @query_no_truncate = 1,
    @attach_query_result_as_file = 0;
ELSE PRINT 'No results, don't email';

Answer 2

我使用如果存在。有效的代码

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='[e-mail]',
    @subject = 'WARNING',
    @body = @html,
    @body_format = 'HTML',
    @query_no_truncate = 1,
    @attach_query_result_as_file = 0;
    END;
GO