Wrap select with union operator for dbmail in format HTML

Question

I have created a query to send mail in HTML format, with tables.

Then I had to add another query to my query using the union statement.

There I ran into the problem that the FOR XML clause cannot be used with union.

Following the suggestion of other users, I tried to wrap my query in the following way (unionselect):

    DECLARE @cuerpo NVARCHAR(max)
    DECLARE @tuplas int
    DECLARE @profile char(20)
    DECLARE @lista_distribucion char(200)
    DECLARE @control char(300)
    DECLARE @separador char(1) = CHAR(9)
    DECLARE @query2 varchar(2048)
    
    begin 
    execute as login = 'sige_java'
    
    set @profile='SIGE'
    set @lista_distribucion='[email protected]'
    set @control='ASDASDASD'
    
    SET @Cuerpo = N'<style type="text/css">
                        h2, 
                        body {
                            font-family: Arial, sans-serif;
                        }
        
                        table {
                            margin: 0 auto;
                            border-collapse: collapse;
                        }
        
                        table td {
                            padding: 6px;
                            border: 3px solid white;
                            background-color:#ffffff;
                            color:#000000;
                            font-size:11px;
                            text-align: center;
                        }
        
                        table th {
                            padding: 6px;
                            border: 3px solid white;
                            background-color:#cc0000;
                            color:#ffffff;
                            font-size:10px;
                            font-weight: bold;
                        }        
                    </style>' 

        + N'<table border="1">' + N'<tr>
        <th>Fecha de inicio</th>
        <th>Producto</th>
        <th>OBS</th>
        <th>Precio U$S</th>
        <th>Precio $</th>
        <th>Nombre</th>' +
        
        CAST (
                (
                SELECT ( -- WRAP QUERY UNION
                    SELECT 
                        TD = cast(a.FAPromocionFchIni as date), '',
                        TD = b.FAPromocionPrdId, '',
                        TD = 'Ingreso Oferta Pesos $', '',
                        TD = '----', '',
                        TD = CONVERT(varchar,b.FAPromocionPrecio*1.22,103), '',
                        TD = c.PrdDsc, ''
                        from  [FIVISA].[dbo].FAPROMOCIONES a 
                        join [FIVISA].[dbo].FAPROMOCIONESPRODUCTOS b  on a.FAPromocionId=b.FAPromocionId and b.FAPromocionPrdActivo=1
                        join [FIVISA].[dbo].PRODUC c on b.FAPromocionPrdId=c.PrdId
                        where a.FaPromocionEstado='ING' and a.FAPromocionMonId=0000
                        and FAPromocionFchIni between dateadd(day,-7,GETDATE()) and GETDATE()
                    UNION
                    SELECT 
                        TD = cast(a.FAPromocionFchIni as date), '', 
                        TD = b.FAPromocionPrdId, '', 
                        TD = 'Cambio Precio Oferta', '', 
                        TD = '----', '', 
                        TD = '----', '', 
                        TD = c.PrdDsc, '' 
                        from  [FIVISA].[dbo].FAPROMOCIONES a 
                        join [FIVISA].[dbo].FAPROMOCIONESPRODUCTOS b on a.FAPromocionId=b.FAPromocionId and b.FAPromocionPrdActivo=1
                        join [FIVISA].[dbo].PRODUC c on b.FAPromocionPrdId=c.PrdId
                        where  a.FAPromocionFchFin  between dateadd(day,-7,GETDATE()) and GETDATE()         
               ) as unionselect
                    FOR XML PATH('tr'), TYPE
                    ) AS NVARCHAR(MAX)
               ) + '</b>' +
                    N'</table>';
                    
----ENVIO DEL EMAIL 
EXEC msdb.dbo.sp_send_dbmail     
@recipients = @lista_distribucion,  
@subject = @control,  
@body = @Cuerpo,  
@body_format = 'HTML',  
@profile_name = @profile;   
        
end;

The error that returns is:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Am I wrapping it correctly?

Answer 1

I was able to solve as follows:

DECLARE @cuerpo NVARCHAR(max)
DECLARE @tuplas int
DECLARE @profile char(20)
DECLARE @lista_distribucion char(500)
DECLARE @control char(300)
DECLARE @separador char(1) = CHAR(9)
DECLARE @query2 varchar(2048)

begin 
execute as login = 'sige_java'

set @profile ='SIGE'
set @lista_distribucion = '[email protected]'
set @control = 'FIVISA: Actualizaciones de productos en promoción'

SET @cuerpo = N'<style type="text/css">
                    h2, 
                    body {
                        font-family: Arial, sans-serif;
                    }

                    table {
                        margin: 0 auto;
                        border-collapse: collapse;
                    }

                    table td {
                        padding: 6px;
                        border: 3px solid white;
                        background-color:#ffffff;
                        color:#000000;
                        font-size:11px;
                        text-align: center;
                    }

                    table th {
                        padding: 6px;
                        border: 3px solid white;
                        background-color:#cc0000;
                        color:#ffffff;
                        font-size:10px;
                        font-weight: bold;
                    }

                </style>' 
+ N'<table border="1">' 
+ N'<tr>
<th>Fecha de inicio</th>
<th>Producto</th>
<th>OBS</th>
<th>Precio U$S</th>
<th>Precio $</th>
<th>Nombre</th>' +

CAST((
    SELECT * from (
        SELECT  
            (select cast(a.FAPromocionFchIni as date) as 'td' for xml path(''), type) as 'Pepe1',
            (select b.FAPromocionPrdId as 'td' for xml path(''), type) as 'Pepe2',      
            (select 'Ingreso Oferta dólares U$S' as 'td' for xml path(''), type) as 'Pepe3',
            (select CONVERT(varchar,b.FAPromocionPrecio*1.22,103) as 'td' for xml path(''), type) as 'Pepe4',
            (select '----' as 'td' for xml path(''), type) as 'Pepe5',
            (select c.PrdDsc as 'td' for xml path(''), type) as 'Pepe6'
            from  [FIVISA].[dbo].FAPROMOCIONES a (nolock)
            join [FIVISA].[dbo].FAPROMOCIONESPRODUCTOS b (nolock) on a.FAPromocionId=b.FAPromocionId and b.FAPromocionPrdActivo=1
            join [FIVISA].[dbo].PRODUC c (nolock) on b.FAPromocionPrdId=c.PrdId
            where a.FaPromocionEstado='ING' and a.FAPromocionMonId=2222
            and FAPromocionFchIni between dateadd(day,-7,GETDATE()) and GETDATE()
        UNION ALL
        SELECT 
            (select cast(a.FAPromocionFchIni as date) as 'td' for xml path(''), type) as 'Pepe7',
            (select b.FAPromocionPrdId as 'td' for xml path(''), type) as 'Pepe8',
            (select 'Ingreso Oferta Pesos $' as 'td' for xml path(''), type) as 'Pepe9',            
            (select '----' as 'td' for xml path(''), type) as 'Pepe10',
            (select CONVERT(varchar,b.FAPromocionPrecio*1.22,103) as 'td' for xml path(''), type) as 'Pepe11',
            (select c.PrdDsc as 'td' for xml path(''), type) as 'Pepe12'
            from  [FIVISA].[dbo].FAPROMOCIONES a (nolock)
            join [FIVISA].[dbo].FAPROMOCIONESPRODUCTOS b (nolock) on a.FAPromocionId=b.FAPromocionId and b.FAPromocionPrdActivo=1
            join [FIVISA].[dbo].PRODUC c (nolock) on b.FAPromocionPrdId=c.PrdId
            where a.FaPromocionEstado='ING' and a.FAPromocionMonId=0000
            and FAPromocionFchIni between dateadd(day,-7,GETDATE()) and GETDATE()           
    ) as K 
    order by cast(k.Pepe3 as nvarchar(max)) desc,  cast(k.Pepe2 as nvarchar(max))
    FOR XML path('tr'), type) AS NVARCHAR(MAX)) + '</b>' + N'</table>';
            
    ----ENVIO DEL EMAIL 
     EXEC msdb.dbo.sp_send_dbmail     
     @recipients = @lista_distribucion,  
     @copy_recipients ='[email protected];[email protected];[email protected]',
     @subject = @control,  
     @body = @Cuerpo,  
     @body_format = 'HTML',  
     @profile_name = @profile;  

end;