Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2002-01-23 : 14:03:30
|
| I really appreciate all of the help people have been giving me on here. I've got another question, of course, and it's probably pretty basic.I need to set a trigger on a table that will notify various people by email when certain conditions are true. I'm fine with the trigger and the updated table, and I've got a nice send mail stored procedure... but is there any better way to send email to a bunch of people (the results of a query) other than populating a temp table (or table variable) and then iterating through it with a cursor?Thought I'd check before I run off and do it the bad way, for once.Cheers-b |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-23 : 17:52:17
|
| If you put an identity column on the generated table you don't need a cursor.You could also use the csv string creation method to build a distribution list.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
 |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-01-24 : 23:27:03
|
| Thanks... I can't use the CSV approach because the email generated needs to be heavily personalized and could well go to hundreds of people (each with a different message).However I'm intrigued by your comment about an identity column on the generated table; I can definitely see how to generate a table of email addresses and messages, but how can I have my send email stored procedure process each one without using a cursor?As I'm typing this, I think I may see the answer; use a while loop to count up the records to the max of the identity field and call the SP for each one?Thanks-b |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-25 : 03:39:01
|
| That's rightdeclare @id intset @id = 0while @id < (select max(id) from tbl)beginselect @id = min(id) from tbl where id > @idselect @recipient = recipient, ...from tblwhere id =@id...endIt won't be faster than the cursor probably but does give you the opertunity to process all records if you need to at some later date and it means you have control of the locking.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
One more Q: batch SP's without cursor?