asked on

Syntax error with @Query parameter in e-mail with select statement

Cast question in a @query statment for e-mail.
exec msdb..sp_send_dbmail --@recipients = @copymembers,
@profile_name = 'Mail Test',
@recipients = 'dm3295@att.com',
@body = 'The following error(s) need to be investigated: ',
@query = 'select * from dbProcess..tbNumOfFilesinDirectories where fdUpdateDate =' + cast(@DateTime as varchar),
@Subject = 'Action: dbProcess..tbNumOfFIlesinDirectories Error(s) Need Investigation.'
Error:
Incorrect syntax near '+'.
I tried numerous set ups with moving quotes around and put () around entire statement and I can't get the error to go away. Sorry, I am a beginner and getting frustrated.

Guy Hengel [angelIII / a3]

declare @sql varchar(200)
set @sql = 'select * from dbProcess..tbNumOfFilesinDirectories where fdUpdateDate =' + cast(@DateTime as varchar)
exec msdb..sp_send_dbmail --@recipients = @copymembers,
@profile_name = 'Mail Test',
@recipients = 'dm3295@att.com',
@body = 'The following error(s) need to be investigated: ',
@query = @sql
@Subject = 'Action: dbProcess..tbNumOfFIlesinDirectories Error(s) Need Investigation.'

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Daniel Reynolds

try declaring the @query parameter before the call.

i.e.:

declare @query varchar(250),
@DateTime datetime
SET @DateTime = getdate();
SET @query = 'select * from dbProcess..tbNumOfFilesinDirectories where fdUpdateDate =' + cast(@DateTime as varchar)
--Now do the call
exec msdb..sp_send_dbmail --@recipients = @copymembers,
@profile_name = 'Mail Test',
@recipients = 'dm3295@att.com',
@body = 'The following error(s) need to be investigated: ',
@query,
@Subject = 'Action: dbProcess..tbNumOfFIlesinDirectories Error(s) Need Investigation.'

bamboo7431

there is no type varchar
there are types varchar(10), varchar(12), etc...

Guy Hengel [angelIII / a3]

>there is no type varchar
actually, you can declare are variable like that:
declare @var varchar

which will be the same as
declare @var varchar(1)
...