使用UNION封装查询生成HTML格式DBMail时出错，求校验封装方式

问题描述 #

我编写了一个用于生成带HTML表格的邮件内容的SQL查询，后续需要通过UNION语句合并另一个查询结果，但遇到FOR XML子句无法直接与UNION配合使用的问题。按照其他用户的建议尝试封装查询后，运行报错。

错误代码 #

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='fmartinez@fivisa.com.uy'
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;

错误信息 #

当子查询未用EXISTS引入时，选择列表中只能指定一个表达式。

解决方法 #

你的封装方式不正确，问题出在额外嵌套的SELECT (...) as unionselect这一层。SQL Server不允许在单个SELECT列中返回多列的子查询结果，而你UNION后的查询返回了多列（对应多个<TD>节点），因此触发报错。

正确的做法是直接对UNION合并后的结果使用FOR XML PATH('tr'), TYPE，不需要额外套一层SELECT别名。修改后的核心部分如下：

CAST (
        (
        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()         
        FOR XML PATH('tr'), TYPE
        ) AS NVARCHAR(MAX)
   ) + N'</table>';

另外注意原代码末尾有个多余的</b>标签，建议去掉，避免HTML结构错误。

内容的提问来源于stack exchange，提问作者Cómputos