如何将游标结果作为参数传入send_dbmail？求优化建议

Hey there! You’re totally right to question using a cursor here—cursors are almost never the most efficient approach for set-based operations in SQL Server, and they can lead to performance headaches (not to mention syntax risks like you’ve run into). Let’s break down how to fix this.

First, Let’s Spot the Issues in Your Current Code #

• Cursor overhead: Cursors process rows one at a time, which is slow if your PMtable has even a moderate number of records.
• Unsafe dynamic SQL: Your code concatenates @PM directly into a query string, which will break if PM has special characters (like apostrophes) and opens you up to SQL injection risks. Plus, you’re missing single quotes around the string value—this would throw a syntax error right now!
• Redundant variable declaration: Declaring @emailBody inside the loop is unnecessary and adds unnecessary overhead.

Better Approach: Replace Cursors with Set-Based Logic + Parameterized Queries #

If your goal is to send a separate email for each PM with their corresponding othertable data, you can ditch the cursor entirely and use a table variable with a WHILE loop (far more efficient than cursors) paired with parameterized queries to avoid injection risks.

Here’s the revised code:

-- Step 1: Store all PMs in a table variable for easy iteration
DECLARE @PMList TABLE (
    PM varchar(50),
    IsProcessed BIT DEFAULT 0
)
INSERT INTO @PMList (PM)
SELECT PM FROM PMtable

-- Step 2: Iterate through each PM without a cursor
DECLARE @CurrentPM varchar(50)

WHILE EXISTS(SELECT 1 FROM @PMList WHERE IsProcessed = 0)
BEGIN
    -- Grab the next unprocessed PM
    SELECT TOP 1 @CurrentPM = PM 
    FROM @PMList 
    WHERE IsProcessed = 0

    -- Send the email with parameterized query (safe and syntax-proof)
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'Your_Mail_Profile_Name', -- Replace with your actual mail profile
        @recipients = 'recipient@domain.com', -- Add recipient logic if tied to specific PMs
        @subject = 'Data Summary for PM: ' + @CurrentPM,
        @query = 'SELECT * FROM othertable WHERE PM = @TargetPM ORDER BY PM',
        @query_parameters = '@TargetPM varchar(50)',
        @parameter_value = @CurrentPM,
        @body_format = 'HTML' -- Optional: Use HTML for nicer, formatted tables
        -- Add other email parameters (cc, bcc, attachment, etc.) as needed

    -- Mark this PM as processed to avoid re-sending
    UPDATE @PMList 
    SET IsProcessed = 1 
    WHERE PM = @CurrentPM
END

Why This Works Better #

• No cursor overhead: The WHILE loop operates on a set of data instead of row-by-row, which SQL Server optimizes far better.
• Safe parameterized queries: By using @query with @query_parameters, you avoid SQL injection and automatically handle special characters (like apostrophes) in PM values.
• Cleaner, maintainable code: The logic is straightforward, and you eliminate redundant variable declarations.

Bonus: If You Need a Single Email with All PM Data #

If your goal was to combine all PM results into one email instead of separate ones, you can use STRING_AGG (SQL Server 2017+) or FOR XML PATH to aggregate data without any loops at all. For example:

DECLARE @CombinedBody nvarchar(max)

-- Aggregate all PM data into a single formatted HTML string
SELECT @CombinedBody = STRING_AGG(
    '<h3>PM: ' + PM + '</h3>' + 
    (SELECT * FROM othertable ot WHERE ot.PM = p.PM FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'),
    '<hr>'
)
FROM PMtable p

-- Send the combined summary email
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Your_Mail_Profile_Name',
    @recipients = 'recipient@domain.com',
    @subject = 'All PM Data Summary',
    @body = @CombinedBody,
    @body_format = 'HTML'

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