如何重写sql语句？

Question

我有一个sql语句，用于发送邮件。输出如下所示。

    select 'mail body content-1' as 'mailBody','11/9/221' as 'mailFooter','a@a.com' as 'mail'
    union all
    select 'mail body content-2' as 'mailBody','11/09/2021' as 'mailFooter','b@b.com' as 'mail'
    union all
    select 'mail body content-3' as 'mailBody','10/09/2021' as 'mailFooter','a@a.com' as 'mail'

​

​

在这里，我通过"group“为每个邮件发送邮件，并将数据与"xml”相结合。

我的完整代码如下

DECLARE @mail varchar(255)=''
DECLARE @mailBody nvarchar(max)=''
DECLARE db_cursor CURSOR FOR

    select m.mail from (
        --sql String
        select 'mail body content-1' as 'mailBody','11/9/221' as 'mailFooter','a@a.com' as 'mail'
        union all
        select 'mail body content-2' as 'mailBody','11/09/2021' as 'mailFooter','b@b.com' as 'mail'
        union all
        select 'mail body content-3' as 'mailBody','10/09/2021' as 'mailFooter','a@a.com' as 'mail'
        --sql String/
    ) m group by m.mail

---------------------------------------------------------------------------------------------------------------------------- Mail Cursor
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO  @mail

WHILE @@FETCH_STATUS = 0  
BEGIN



set @mailBody=(select c.mailBody as 'span',c.mailFooter as 'small' from (
        --sql String
        select 'mail body content-1' as 'mailBody','11/9/221' as 'mailFooter','a@a.com' as 'mail'
        union all
        select 'mail body content-2' as 'mailBody','11/09/2021' as 'mailFooter','b@b.com' as 'mail'
        union all
        select 'mail body content-3' as 'mailBody','10/09/2021' as 'mailFooter','a@a.com' as 'mail'
        --sql String/
) c where c.mail=@mail
for XML PATH ('div')
)
    select  @mail,@mailBody
    --EXEC msdb.dbo.sp_send_dbmail


FETCH NEXT FROM db_cursor INTO @mail
END

CLOSE db_cursor
---------------------------------------------------------------------------------------------------------------------------- Mail Cursor/

DEALLOCATE db_cursor 

因此，它给了我以下输出。

​

​

我在这里的问题是，我必须写同样的"sql字符串“句子2次。

将

1. 分组为
2. 以在"for路径“

中使用

我怎么才能摆脱这种复发呢？

"sql字符串“不断变化。但是在每个sql语句中都有一个名为"mail“的字段。我计划把它作为一个程序来保存，并一直使用它。

我的路是正确的吗？

我正在使用Server 2016。

我还没有决定如何处理String，有条件的“为什么”循环，sp_execute，插入@tempTable。

Answer 1

可以使用with块为循环生成xml路径：

DECLARE @mail varchar(255)=''
DECLARE @mailBody nvarchar(max)='';
DECLARE db_cursor CURSOR FOR

    with tmp as (
        --sql String
        select 'mail body content-1' as 'mailBody','11/9/221' as 'mailFooter','a@a.com' as 'mail'
        union all
        select 'mail body content-2' as 'mailBody','11/09/2021' as 'mailFooter','b@b.com' as 'mail'
        union all
        select 'mail body content-3' as 'mailBody','10/09/2021' as 'mailFooter','a@a.com' as 'mail'
        --sql String/
    )
    select m.mail, (select c.mailBody as 'span',c.mailFooter as 'small' from tmp c where c.mail=m.mail for XML PATH ('div'))
    from tmp m group by m.mail;

---------------------------------------------------------------------------------------------------------------------------- Mail Cursor
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO  @mail, @mailBody

WHILE @@FETCH_STATUS = 0  
BEGIN

    select  @mail,@mailBody
    --EXEC msdb.dbo.sp_send_dbmail

FETCH NEXT FROM db_cursor INTO @mail, @mailBody
END

CLOSE db_cursor
---------------------------------------------------------------------------------------------------------------------------- Mail Cursor/

DEALLOCATE db_cursor