如何在@body for SQL Server中声明变量？
如何在@body for SQL Server中声明变量？
提问于 2019-01-21 23:51:55
我正在尝试通过SQL Server发送电子邮件。一切正常，但是我不能让@body接受我声明的变量。
我还尝试调用一个新变量并将其设置为@body，但我遇到了同样的问题。
Declare @Body varchar(max),
@TableHead varchar(max),
@TableTail varchar(max),
@message as varchar(max)
Set @message=
(
DECLARE @CNT as int, @SLS as NVARCHAR(10)
select [employeeid], [Sales]
into #loctempemployee from tblEmployees
Set @CNT = (Select COUNT (Distinct EmployeeID) from #loctempemployee)
SELECT tr.Principal As [TD], tr.[Company Name] As [TD], ai.[Action Item] As [TD], ai.Owners As [TD], ai.[Due Date] As [TD], ai.Updated As [TD]
FROM [tblActionItem] ai
INNER JOIN tblTripReport tr ON ai.TripReportID = tr.tripreportID
INNER JOIN tblCustomers cu ON cu.CustomerID = tr.[Customer ID]
INNER JOIN tblEmployees em ON em.EmployeeID = cu.EmployeeID
WHERE em.Sales = (Select sales from #loctempemployee Where EmployeeID = (Select top 1 EmployeeID from #loctempemployee))
For XML raw('tr'), Elements
Delete #loctempemployee Where EmployeeID = (Select top 1 EmployeeID from #loctempemployee)
set @CNT = @CNT -1;
End
drop table #loctempemployee
)
Select @Body = (@message)回答 2
Stack Overflow用户
回答已采纳
发布于 2019-01-22 00:16:33
如果我没理解错的话，您试图将SELECT的结果赋值给@message。您使用的语法不正确。您不能像在TSQL中那样在SET中运行一批语句。只要查询生成字符串结果，就可以将查询结果赋给@message。因此，我认为您可能希望将代码更改为如下所示(从SET语句中提取其他语句)。
Declare @Body varchar(max),
@TableHead varchar(max),
@TableTail varchar(max),
@message as varchar(max);
DECLARE @CNT as int, @SLS as NVARCHAR(10);
select [employeeid], [Sales]
into #loctempemployee from tblEmployees;
Set @CNT = (Select COUNT (Distinct EmployeeID) from #loctempemployee);
Set @message=
(
SELECT tr.Principal As [TD], tr.[Company Name] As [TD], ai.[Action Item] As [TD], ai.Owners As [TD], ai.[Due Date] As [TD], ai.Updated As [TD]
FROM [tblActionItem] ai
INNER JOIN tblTripReport tr ON ai.TripReportID = tr.tripreportID
INNER JOIN tblCustomers cu ON cu.CustomerID = tr.[Customer ID]
INNER JOIN tblEmployees em ON em.EmployeeID = cu.EmployeeID
WHERE em.Sales = (Select sales from #loctempemployee Where EmployeeID = (Select top 1 EmployeeID from #loctempemployee))
For XML raw('tr'), Elements
);
Delete #loctempemployee Where EmployeeID = (Select top 1 EmployeeID from #loctempemployee);
set @CNT = @CNT -1;
drop table #loctempemployee;
Select @Body = (@message);还有，你是不是在一个没有显示的循环中做这个？有一个END没有对应的BEGIN，这也可以解释set @CNT = @CNT -1;这一行。
Stack Overflow用户
发布于 2019-01-22 00:16:05
注释是非常正确的-您不能在对单个数据类型的变量赋值中执行巨大的SQL块。
如果你把你想要的数据放到一个#temp表中，你可以使用下面这样的东西。
DECLARE @xml VARCHAR(MAX);
SET @xml = CAST(( SELECT [col1] AS 'td','',[col2] AS 'td','', [col3] AS 'td'
FROM #temptable
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>(Report Title)</H3>
<table border = 1>
<tr>
<th> Col1 </th> <th> Col2 </th> <th> Col3 </th>'
SET @body = @body + @xml + '</table></body></html>'
-- mail out contents
EXEC msdb.dbo.sp_send_dbmail @recipients = @emailaddress,
@body = @body,
@body_format = 'HTML',
@subject = 'SomeReport',
@profile_name = 'SomeProfileName';页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/54293456
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号