Regarding the @query parameter, BOL states:
"Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query."
I'm calling sp_send_dbmail from a code that updates records in a table. I want to send some information about the record that was updated, including data from child tables joined to the updated record.
Since I can't pass a local variable to @query, I'm doing something like:
@query = 'DECLARE @RecordID AS Varchar(12)
SET @RecordID = (select top 1 Record_ID
FROM Table where ColumnChangedFrom1 = 0
ORDER BY Modify_Date DESC);
SELECT t1.column1, t2.column1, t2.column2
FROM Table AS t1
INNER JOIN Table2 t2 on t1.RecordID = t2.T1RecordID
WHERE t1.Record_ID = @RecordID'
While this works so far in testing, I'm not certian that in production it will return the correct records 100 percent of the time. Is there a way to be certain the dbMail query will find the right records?