Local variables in sp_send_dbmail?

Question

I am working on a SQL stored procedure that should send an attachment with query results.

I use sp_send_dbmailto send email.

In the query that I would like to send, I join the table variable. When I executed the stored procedure, I got an error message stating that the variable does not exist.

My code is:

 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'

Can I refer to a local variable in this stored procedure? If not, why not?

TIA!

(I am using SQL Server 2005)

Answer 1

, , - . temp.

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