Passing Cursor Result to send_dbmail as a Parameter

Question

I've never worked with cursors before, and upon reading, this may not be the best approach, so by all means, makes suggestions.

I am attempting to pass the result set of a cursor to a query. Here's what I have so far:

DECLARE @PM varchar(50),
        @c1 as CURSOR
SET @c1 = CURSOR FOR
    SELECT PM
    FROM PMtable

OPEN @c1;
FETCH NEXT FROM @c1 INTO @PM;

WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @emailBody nvarchar(max)
        SET @emailBody = 'SELECT * FROM othertable WHERE PM = ' + @PM + ' ORDER BY PM';
    
        EXEC msdb.dbo.sp_send_dbmail
        @recipients = '[email protected]',
        @subject = 'test',
        @query = @emailBody;

FETCH NEXT FROM @c1 INTO @PM;
END
CLOSE @c1;
DEALLOCATE @c1;

The idea is to send the @emailBody query result set as an email for every result in the cursor. For example, say the cursor returns three results: Bob, Jim, and Joe. I want to loop run the @emailBody query for each result from the cursor and send an email for each result.

When I run the query as is, I receive an error saying:

Msg 22050, Level 16, State 1, Line 0 Error formatting query, probably invalid parameters

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 504 [Batch Start Line 0]

Query execution failed:

Msg 207, Level 16, State 1, Server SERVER, Line 9 Invalid column name 'Bob'.

Msg 207, Level 16, State 1, Server SERVER, Line 1 Invalid column name 'Bob'.

I have no clue what's going on. Any ideas?

Answer 1

You need to add '':

SET @emailBody='SELECT * FROM othertable WHERE PM = ''' + @PM + ''' ORDER BY PM';

Be aware of possible SQL Injection.

---

How it works:

Msg 207, Level 16, State 1, Server SERVER, Line 9 Invalid column name 'Bob'.

SELECT * FROM othertable WHERE PM = Bob ORDER BY PM 
vs.
SELECT * FROM othertable WHERE PM = 'Bob' ORDER BY PM

Please keep in mind that ORDER BY PM for one value does not change anything.