0

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
Bridge
  • 29,818
  • 9
  • 60
  • 82
user3229811
  • 25
  • 4

1 Answers1

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'
cdev
  • 166
  • 5
  • It seems to me like your solution should work, but I get this error: 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 517 Query execution failed: Msg 105, Level 15, State 1, Server WIN-O9R9U2CPD6B, Line 2 Unclosed quotation mark after the character string 'i '. Msg 102, Level 15, State 1, Server WIN-O9R9U2CPD6B, Line 2 Incorrect syntax near 'i '. – user3229811 Jan 27 '14 at 19:56
  • I made the @qry string size too small and it was truncating the string. I updated the example accordingly. – cdev Jan 30 '14 at 17:43
  • Yes, I set @qry VARCHAR(500) and it works great. Thanks. – user3229811 Feb 03 '14 at 14:39