Need to pass a parameter within a stored procedure to sp_send_dbmail @query

Question

I have a stored procedure that executes a query and then calls dbmail. The mail message includes the results of the query that is executed. I want to pass a parameter/variable to the query in both places: the stored procedure and the @query in dbmail. Below is how I would like to run it, but I get an error since the parameter (@Threshold) is not passed to the dbmail block. How can I do this?

ALTER PROCEDURE [dbo].[spMyProcedure] @Threshold float

AS

IF EXISTS (SELECT Fields FROM Table
WHERE DataValue < @Threshold AND LocalDateTime >= DATEADD(hour, -24, SYSDATETIME())
GROUP BY Fields)

BEGIN

SET NOCOUNT ON

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Profile',
@from_address = 'data@data.com',
@recipients = 'data@data.com',
@subject = 'Data Warning',
@body = 'The following results are outside the defined range.',
@query = 'SELECT Fields FROM Table
WHERE DataValue < @Threshold AND LocalDateTime >= DATEADD(hour, -24, SYSDATETIME())
GROUP BY Fields' ;

END

Answer 1

You are passing a string to the @query parameter so it does not know the value of @Threshold. You need to create a string variable and include the actual value. You can then pass that variable to the sp_send_dbmail procedure.

DECLARE @qry varchar(MAX)
SET @qry = 'SELECT Fields FROM Table WHERE DataValue < ' 
+ cast(@Threshold as varchar) + 
' AND LocalDateTime >= DATEADD(hour, -24, SYSDATETIME()) GROUP BY Fields'