SQL 2005 dbmail trouble with parameters

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.

(parameter declarations)


declare curs1 cursor for    

SELECT distinct [email] from DB.dbo.table88
where email is not null

   
open curs1  
   
fetch next from curs1 into @email
while @@fetch_status = 0    
begin    
                   
              
                     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
end    
close curs1    
deallocate curs1




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?
LVL 6
RustyZ32Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
LowfatspreadConnect With a Mentor Commented:
what do you want it to do with multiple results?

how do you want the body formatted in that case?

why are you using a cursor for this?

can you not send to all the users at once ....

format a comma separated to list or use the cc/bcc options?


normally i'd expect something like this

 set @bodytext=''
 Select @bodytext =  @bodytext+' '
                     + [name] + '        '+[dates]
   FROM DB.dbo.view2
 WHERE [semail] = @email
 order by [dates]

set @bodytext= (SELECT bod from DB.dbo.settings) + ' '+@bodytext
0
 
RustyZ32Author Commented:
I am using a cursor because there can be up to 50 or recipients, and each recipient has to recieve a different set of results (based on who works under them).

for the recipients with multiple people working under that have entries in view2 I need the body to list each:

usera     9/12/2011
userb     9/23/2011

I know the @query parameter in sp_send_dbmail would be nice for this but apparently you cannot pass parameters to it from outside the SP


I tried your suggestion, it works but I will have to tweak the spaces and returns to make it display right.  
0
 
LowfatspreadCommented:
you need to add char(10)+char(13)   crlf to add line breaks at the appropiate places...

(maybe the other way around 13+10)

@query
no you have to form the sql string completly and pass it to dbmail... you cant concatenate the variables on the exec...
0
All Courses

From novice to tech pro — start learning today.