关于sp_send_dbmail实现收件人按钮显隐及不同正文的技术问询

Great question! Let's tackle your two requirements one by one since sp_send_dbmail doesn't support these out of the box, but we can work around it with some straightforward approaches.

The core limitation here is that a single call to sp_send_dbmail can only use one body content for all recipients (To and CC combined). To send unique bodies to each group, you'll need to call the stored procedure twice: once for your primary To recipients, and once for your CC recipients.

To make this clean and reusable, wrap the logic in a custom stored procedure:

CREATE PROCEDURE dbo.SendDualBodyDBMail
    @profile_name NVARCHAR(128),
    @to_recipients NVARCHAR(MAX),
    @cc_recipients NVARCHAR(MAX),
    @body_to NVARCHAR(MAX),
    @body_cc NVARCHAR(MAX),
    @subject NVARCHAR(255)
AS
BEGIN
    SET NOCOUNT ON;

    -- Send to primary To recipients with their specific body
    IF @to_recipients IS NOT NULL AND LTRIM(RTRIM(@to_recipients)) <> ''
    BEGIN
        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = @profile_name,
            @recipients = @to_recipients,
            @subject = @subject,
            @body = @body_to,
            @body_format = 'HTML'; -- Use 'TEXT' if you don't need HTML
    END

    -- Send to CC recipients with their unique body
    IF @cc_recipients IS NOT NULL AND LTRIM(RTRIM(@cc_recipients)) <> ''
    BEGIN
        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = @profile_name,
            @recipients = @cc_recipients, -- Treat CC list as direct recipients here
            @subject = @subject,
            @body = @body_cc,
            @body_format = 'HTML';
    END
END

Usage Example: #

EXEC dbo.SendDualBodyDBMail
    @profile_name = 'VoiceSQLMail',
    @to_recipients = 'test@co.uk',
    @cc_recipients = 'test1@co.uk',
    @body_to = '<p>Hi! It Works! No Sales Were Made Today</p>',
    @body_cc = '<p>Hi! This is the CC-only version of the daily update</p>',
    @subject = 'Daily Sales Notification';

To control button visibility in the email body, you'll need to use HTML-formatted emails (set @body_format = 'HTML') and dynamically construct the body content based on your scenario.

For example, if you have two scenarios:

• Scenario 1: Show the "Recipient" button, hide the "CC" button
• Scenario 2: Hide the "Recipient" button, show the "CC" button

Use a conditional statement to build the HTML body:

DECLARE @scenario INT = 1; -- Set to 1 or 2 based on your use case
DECLARE @dynamic_body NVARCHAR(MAX);

-- Build the HTML body with conditional buttons
SET @dynamic_body = '<html>
<head>
    <style>button { padding: 8px 16px; background: #0078d4; color: white; border: none; border-radius: 4px; }</style>
</head>
<body>
    <h2>Daily Update</h2>
    <p>Your core email content goes here...</p>
    
    ' + CASE WHEN @scenario = 1 THEN '<button onclick="window.location.href=''https://your-action-url.com''">Recipient Action Button</button>' ELSE '' END + '
    ' + CASE WHEN @scenario = 2 THEN '<button onclick="window.location.href=''https://your-cc-action-url.com''">CC Action Button</button>' ELSE '' END + '
</body>
</html>';

-- Send the scenario-specific email
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'VoiceSQLMail',
    @recipients = 'test@co.uk', -- Or CC list for scenario 2
    @subject = 'Scenario-Based Action Required',
    @body = @dynamic_body,
    @body_format = 'HTML';

Combining Both Requirements #

If you need to pair different bodies with scenario-based buttons, just merge the two approaches: call the custom stored procedure twice, each time passing a dynamically constructed body tailored to the recipient group and scenario.

内容的提问来源于stack exchange，提问作者User