用于发送电子邮件的 SQL Server 存储过程

Question

这是我第一次尝试编写一个向某人发送电子邮件的存储过程。当尝试执行时，我收到以下错误:

Msg 102, Level 15, State 1, Procedure EmailTodaysErrors, Line 14
Incorrect syntax near '@MailServer'.
Msg 137, Level 15, State 2, Procedure EmailTodaysErrors, Line 26
Must declare the scalar variable "@mailserver".
Msg 137, Level 15, State 2, Procedure EmailTodaysErrors, Line 33
Must declare the scalar variable "@Body".

我正在使用的导致此问题的代码是:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE EmailTodaysErrors 
@SenderName varchar(100),
@SenderAddress varchar(100),
@RecipientName varchar(100),
@RecipientAddress varchar(100),
@Subject varchar(200),
@Body varchar(8000)
@MailServer varchar(100) = 'localhost'
AS
SET NOCOUNT ON;
declare @oMail int --Object reference
declare @resultcode int
EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT

if @resultcode = 0
BEGIN
EXEC @resultcode = sp_OASetProperty @oMail, 'RemoteHost',  @mailserver
EXEC @resultcode = sp_OASetProperty @oMail, 'FromName', @SenderName
EXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress',  @SenderAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'From', @SenderAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'To', @RecipientAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body
EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL
EXEC sp_OADestroy @oMail
END

set nocount off
GO

Answer 1

您在 @body 行后面缺少一个逗号，这会导致您的声明失效。

在此处添加:

@Body varchar(8000), -- HERE
@MailServer varchar(100) = 'localhost'