How to assign sql parameter with many values returned from SQL statement

See more:

SQL-Server

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;

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