SQL 2005 dbmail trouble with parameters
Posted on 2011-09-27
I am using sp_send_dbmail within a cursor, but cannot set parameters based on query results unless the result returns only a single value.
declare curs1 cursor for
SELECT distinct [email] from DB.dbo.table88
where email is not null
fetch next from curs1 into @email
while @@fetch_status = 0
set @bodytext = (SELECT bod from DB.dbo.settings) + '
' + (SELECT [name] + ' '+[dates] FROM DB.dbo.view2
WHERE [semail] = @email)
set @subjectline = (SELECT sub from DB.dbo.settings)
EXEC msdb.dbo.sp_send_dbmail @recipients= @email, @body=@bodytext,
@subject = @subjectline, @profile_name = 'profileA'
fetch next from curs1 into @supemail
this works as desired but only if the 2nd query in the @bodytext parameter returns a single result (because subqueries can only have single results).
any ideas to make this work with multiple results?