Solved

SQL 2005 dbmail trouble with parameters

Posted on 2011-09-27
3
255 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:RustyZ32
  • 2
3 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 36710810
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
 
LVL 6

Author Comment

by:RustyZ32
ID: 36710926
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36711960
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

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now