Local variables in sp_send_dbmail?

Question

I'm working on an SQL stored procedure that is supposed to send an attachment with the results of a query.

I am using sp_send_dbmail to send the email.

Within the query I'd like to send, I join the to a table variable. When I executed the stored procedure, I got an error message which said that the variable didn't exist.

My code:

 DECLARE @t TABLE (
    id INT IDENTITY(1,1),
    some fields
 )

DECLARE @query VARCHAR(MAX)
SET @query =  'SELECT 
    some values
 FROM @t t
  INNER JOIN dbo.Table d ON t.field = d.field
EXEC msdb.dbo.sp_send_dbmail @recipients=@recipients_list,
        @subject = @subject,
        @query = @query,
        @attach_query_result_as_file = 1, 
        @query_result_width = 4000, 
        @query_attachment_filename = 'Details.txt'

Is there any way for me to refer to the local variable within this stored procedure? If not, why not?

TIA!

(I am using SQL Server 2005)

Answer 1

The query runs in a different context than your original code body, so it is not aware of any local variables. Try using a global temp table instead.

CREATE TABLE ##t (
    id INT IDENTITY(1,1),
    some fields
 )

DECLARE @query VARCHAR(MAX)
SET @query =  'SELECT 
    some values
 FROM ##t t
  INNER JOIN dbo.Table d ON t.field = d.field'
EXEC msdb.dbo.sp_send_dbmail @recipients=@recipients_list,
        @subject = @subject,
        @query = @query,
        @attach_query_result_as_file = 1, 
        @query_result_width = 4000, 
        @query_attachment_filename = 'Details.txt'

DROP TABLE ##t