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 - 2003-01-14 : 18:57:55
|
| I've got a situation where I need to run the same stored procedure somewhere between 500 and 5000 times, each time passing it the results of a query.The easy way seems to be to dump the results of the query into a temp table (can't be a table variable because the query itself is a variable), and then use a cursor to iterate through the temp table and run the SP on each row. I hate using temp tables, and I hate cursors. Am I missing something here?The size of the query and the number of times it must be run precludes just building a big varchar with all of the exec's in it.Thanks-b |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2003-01-14 : 19:21:42
|
| maybe if you provide more information about what you are trying to do and some table structure and sample data and expected output we can help you with a solution that is set based. |
 |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2003-01-14 : 19:38:44
|
Sure -- that makes sense.  Ok, the procedure I need to run plunks an email into a user's (DB-based) mailbox. The procedure needs to be run 500 to 5000 times, with only the parameter for who to send the email to changing. For example,exec p_SendEmail @iUsersTo=[user id],@iUsersFrom=1,@vcSubject='test subject',@vcEmail='...body...' What I need to do is run that procedure, substituting the result of another user-specified query for the [user id]. The procedure I've written takes a query, a subject line, and an email, then runs the query and sends a copy of the email to each result from the query. For instance:exec p_SendBulkEmail 'select user_id from users where state=''GA''','Greetings, user from Georgia','Body of email'. Right now, I've got the p_SendBulkEmail procedure creating a temp table, using EXEC() to run the supplied query, and then a cursor to iterate through the temp table and run the p_SendMail procedure for each row.Hope that helps explain it better.Cheers-b |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-01-14 : 19:50:51
|
Well, I'm not saying this is effecient, but here's the way I ususally handle things like this.I write a stored proc that gives me the list of folks to send to, and one that gives me the text/subject to send. I then loop through the recordset/datareader and call a "add email" function and pass it the destination address and the text of the message.After re-reading, you might be able to get away with an insert into..select. This is psudo-code, but you get the idea I think: --Code that creates a temp table from the user defiend query hereINSERT INTO EmailTable(DestAddr, Subject, Body)SELECT DestAddr, @vcSubject, @vcEmailFROM #TempTable Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2003-01-14 : 20:19:27
|
| Hmm... an interesting idea, but unfortunately I need some of the logic that's in the send mail procedure (it decides about whether to send off-site notifications, if the user's mailbox is full, if the user has configured any filtering, etc). In theory, all of that stuff could move into triggers and then directly hitting the table would work, but I'm wary of getting into instantiating activeX objects and such from within triggers. Maybe that's silly, though.Cheers-b |
|
|
|
|
|
|
|
|
Efficient way to exec SP on query results?