I use DB Mail with @query in a stored procedure (below). The query is put in the variable @cmd. The variable @db is passed as a parameter. Lots of quotes and quoting quotes.

Maybe you just have a syntax error with quotes. Maybe you have to include USE DB in your query. Mine runs a dbcc so it can be called from any db. Also the MSDB database must have the Service Broker turned on for DB Mail.

create procedure myproc (@db varchar(50)) as

declare @subj varchar(100),

@cmd varchar(200),

@file varchar(100)

select@subj = @@servername + ' ' + @db + ' Integrity Check',

@cmd = 'dbcc checkdb(''' + @db + ''') ',

@file = @db + '_dbcc.txt' ;

exec msdb.dbo.sp_send_dbmail

@profile_name = 'SQLAdmin',

@recipients = 'myname@mydomain',

@subject = @subj,

@query = @cmd,

@attach_query_result_as_file = 1,

@query_attachment_filename = @file ;