如何在msdb.dbo.sp_send_dbmail中包含前导空格

我在SQL表中存储了以下数据，现在我想使用msdb.dbo.sp_send_dbmail通过电子邮件发送此表。如何包含前导空格？

CREATE TABLE #Temp2 (
NewBranchesAdded varchar(1), 
branch_code varchar(10), 
Branch varchar(100))
insert #Temp2 values('y','802',' Chicago, IL')
insert #Temp2 values('y','801','Austin, TX')

然后，我的 SQL 存储过程的代码如下：

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
IF(select top 1 [NewBranchesAdded] from #temp) = 'Y'
BEGIN
SET @xml = CAST(( SELECT branch_code AS 'td','',Branch AS 'td'
FROM  #temp ORDER BY branch_code 
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>New Branch(es) added to BI_EDW.dbo.Branch table</H3>
<table border = 1> 
<tr>
<th>BranchCode</th> <th><div align="left">Branch</th></tr>'    
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MGMTReporting', -- replace with your SQL Database Mail Profile 
@body = @body,
@body_format ='HTML',
@recipients = 'user@abc.com', -- replace with your email address
@subject = 'New Branch Monitor Alert!' ;
END

但是我收到的电子邮件看起来像这样：

BranchCode  Branch
801 Chicago, IL
802 Austin, TX

所以它删除了芝加哥的前导空间

我现在想通了。SET @body = @body + 替换(@xml，'，''( +' '

相关内容

最新更新

热门标签：