如何使用动态生成的数据从sql发送自动电子邮件 [英] How do I send an automated email from sql with data that is dynamically generated

问题描述

大家好，





我有一个SP，它会向某些帐户发送密码恢复电子邮件。为了实现相同的我必须附加到正文，一个变量将从数据库中挑选，不能硬编码。请建议如何做同样的事情。



PFB我正在使用的sp，

Hi All,


I have an SP which sends password recovery email to certain accounts. To implement the same I have to attach to the body, a variable which will be picked from the database and CANNOT be hardcoded. Please suggest ways to do the same.

PFB the sp I am using,

CREATE PROCEDURE [dbo].[spGetPassword](
@Username Varchar(50))
AS
BEGIN

DECLARE @Password Varchar(100)
DECLARE @RecEmail Varchar(100)
Select @Password=[Password] From [dbo].[LoginDetails] Where [UserName]=@Username

--For sending emails
Select @RecEmail= Value FROM MemberDetails WHERE EmpName=@Username AND Name='Email'
execute msdb.dbo.sp_send_dbmail
@profile_name='*******',@recipients=@RecEmail,--@copy_recipients =@CopyEmail,
@subject= 'Password Recovery', <b<big>>@body= 'Hi, Your password is',@query='Select Password Where Username=@Username'</big>

END





它工作正常，但查询位没有执行，也许，因为我做错了...请建议成功实现相同的方法。



谢谢

END


It is working fine but the query bit is not executing, maybe, because I am doing it wrong...Please suggest ways to successfully implement the same.

Thanks

推荐答案

试试这个。您的查询的问题是@user名本地变量在@query中不可用，因为它在单独的会话中执行。你已经在@Password中获得了密码，所以我在你的发送邮件中使用它。

Try this. The problem with your query is that @username local variable wont be available in the @query because it is being executed in separate session. You have already got the password in @Password so I am using it in your send mail.

CREATE PROCEDURE [dbo].[spGetPassword](
@Username Varchar(50))
AS
BEGIN

DECLARE @Password Varchar(100)
DECLARE @RecEmail Varchar(100)
DECLARE @bodyMsg nvarchar(max)
Select @Password=[Password] From [dbo].[LoginDetails] Where [UserName]=@Username
set @bodyMsg = 'Hi, Your password is' + @Password

--For sending emails
Select @RecEmail= Value FROM MemberDetails WHERE EmpName=@Username AND Name='Email'

execute msdb.dbo.sp_send_dbmail
@profile_name='*******',
@recipients=@RecEmail,
@subject= 'Password Recovery',
@body = @bodyMsg

END

这篇关于如何使用动态生成的数据从sql发送自动电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！