SQL Server 邮件变量的使用

Question

我想在 SQL Server 上使用发送邮件实用程序，其中收件人变量 (@recipients) 应采用格式化字符串作为值。我想在下面展示一个例子:

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'Mail Profile',
    @recipients = Right('domain/<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="82f7f1e7f0c2e1edeff2e3ecfbace1edef" rel="noreferrer noopener nofollow">[email protected]</a>',
                         CHARINDEX('/',reverse('domain/<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="e590968097a5868a8895848b9ccb868a88" rel="noreferrer noopener nofollow">[email protected]</a>'))-1),
    @body = 'TEST MESSAGE',
    @subject = 'Automated Success Message'

但是当我执行上面的语句时，出现错误消息:关键字Right附近的语法不正确

Answer 1

您不能将表达式作为参数传递给 am SP。您必须传递文字值或变量。您需要首先声明一个变量，设置它的值，然后将其作为参数传递:

DECLARE @recipients nvarchar(255);
SET @recipients = RIGHT('domain/<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="394c4a5c4b795a565449585740175a5654" rel="noreferrer noopener nofollow">[email protected]</a>', CHARINDEX('/', REVERSE('domain/<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="97e2e4f2e5d7f4f8fae7f6f9eeb9f4f8fa" rel="noreferrer noopener nofollow">[email protected]</a>')) - 1);

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Mail Profile',
                             @recipients = @recipients,
                             @body = 'TEST MESSAGE',
                             @subject = 'Automated Success Message';