How can I go about rewriting the sql statement?-CodePudding

I have a sql statement that I use to send mail. The output is as follows as an example.

    select 'mail body content-1' as 'mailBody','11/9/221' as 'mailFooter','[email protected]' as 'mail'
    union all
    select 'mail body content-2' as 'mailBody','11/09/2021' as 'mailFooter','[email protected]' as 'mail'
    union all
    select 'mail body content-3' as 'mailBody','10/09/2021' as 'mailFooter','[email protected]' as 'mail'

Here, I send mail by "group by" for each mail and combining the data with "xml for path".

My full code is as below

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','[email protected]' as 'mail'
        union all
        select 'mail body content-2' as 'mailBody','11/09/2021' as 'mailFooter','[email protected]' as 'mail'
        union all
        select 'mail body content-3' as 'mailBody','10/09/2021' as 'mailFooter','[email protected]' 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','[email protected]' as 'mail'
        union all
        select 'mail body content-2' as 'mailBody','11/09/2021' as 'mailFooter','[email protected]' as 'mail'
        union all
        select 'mail body content-3' as 'mailBody','10/09/2021' as 'mailFooter','[email protected]' 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

As a result it gives me the following output.

My problem here is that I have to write the same "sql string" sentence 2 times.

To group
To use in "for xml path"

How can I get rid of this recurrence?

The "sql string" is constantly changing. But there is a field called "mail" in every sql statement. I plan to save it as a procedure and use it all the time.

Is my path the right path?

I am using SQL Server 2016.

I'm undecided on what to do with String Agg, Conditional "why" loop, sp_execute, insert into @tempTable.

CodePudding user response：

You can use a with block to generate the xml path for the loop:

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','[email protected]' as 'mail'
        union all
        select 'mail body content-2' as 'mailBody','11/09/2021' as 'mailFooter','[email protected]' as 'mail'
        union all
        select 'mail body content-3' as 'mailBody','10/09/2021' as 'mailFooter','[email protected]' 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