如何使用从SQL语句返回的许多值分配sql参数 [英] How to assign sql parameter with many values returned from SQL statement
问题描述
大家好，
我想从数据库邮件发送电子邮件，但这是我第一次处理它，
我试图这样做:
Hi everybody,
I want to send an Email from Database Mail, but it is first time for me to deal with it,
i am try to do like that:
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
(N'<h1>Work Order Report</h1>' +
N'<table border="1">' +
N'<tr><th>Work Order ID</th><th>Product ID</th>' +
N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
N'<th>Expected Revenue</th></tr>' +
CAST (( select Users.UserName from Users ) AS NVARCHAR(MAX) ) +
N'</table>') ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'myProfile',
@recipients = 'myEmail@hotmail.com',
@subject = 'SQL Report',
@body_format = 'HTML',
@execute_query_database = 'DataBaseName',
@body = @tableHTML;
电子邮件已成功发送，但没有正文，并且给我以下错误:
子查询返回了1个以上的值.当子查询遵循=，！=，<，< =，>，> =或将子查询用作表达式时，不允许这样做.
我也想从数据库中选择要在正文中显示的数据，而每一行都位于正文中的单独行中
任何答复将不胜感激
The email is send successfully but with no body and it give me error that:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
also i want to data that selected from Database to be displayed in body whereas every row is located in separate line in body
any reply will be appreciated
Thanks
推荐答案
您必须遍历要获取的表，并必须为邮件创建HTMl.在创建HTML字符串之后，您可以轻松地发送邮件使用下面的代码.
You have to loop through the table which you are getting and have to create HTMl for the mail..and after creating HTML string you can simly send the mail using bellow code.
EXEC sp_send_dbmail
@profile_name = ''MailProfile1'',
@recipients = ''someone@microsoft.com'',
@subject = ''HTML Format'',
@body_format = ''HTML'',
@body = ''<html><body><p>An html link to www.bing.com is embedded in this email. The link is here: <a href="http://www.bing.com">u bing?</a></p></body></html>''
这篇关于如何使用从SQL语句返回的许多值分配sql参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
