| Author |
Topic |
|
t1g312
Posting Yak Master
148 Posts |
 Posted - 2005-03-17 : 06:04:56
|
| Hi all,I need to loop through a certain set of rows from a table A, construct a message using the retrieved fields and send an email to all the addresses stored in another table B. My solution is to use nested cursors - one cusor to hold the rows retrieved from table A and another to store the email addresses from table B. The problem is that @@FETCH_STATUS is a global variable common for all cursors. Is it possible to store the value of @@FETCH_STATUS in a variable before starting the inner loop and set it back after coming out? Is there a better way to acheive the same result, i.e. without using cursors? If yes, how?ThanksAdiAdi-------------------------/me sux @sql server |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-03-17 : 06:14:44
|
| can you use table variables instead (with a field for rowPosition), then using the rowposition, you can retrieve the records that you need in a while loop?--------------------keeping it simple... |
 |
|
|
t1g312
Posting Yak Master
148 Posts |
Posted - 2005-03-17 : 07:07:13
|
| Table variable is more efficient than cursor?Adi-------------------------/me sux @sql server |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-17 : 07:46:57
|
| Using morse code to send your messages is more efficient than using a cursor.You certainly don't need two cursors for this. Create a table like this:CREATE TABLE #emails (address varchar(128) not null, message varchar(500) not null)INSERT INTO #emails(address, message)SELECT A.Email, 'This message is for ' + B.col1 + ', you have won ' + cast(b.col2 as varchar) dollars!!'FROM table1 A INNER JOIN table2 B ON A.ID=B.ID...cursor declaration on #emails table...xp_sendmail loop...drop #emails temp table |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-17 : 08:01:07
|
| You may also want to look at this:http://www.sqlteam.com/item.asp?ItemID=5908This might work better than using xp_sendmail in a loop, especially if you're sending hundreds of emails or more. xp_sendmail is not well suited to mass emailing. While there is a cursor in the application code, you can populate the recordset one time and disconnect it from the database when you loop through it. This will reduce the server resources that a SQL cursor would incur. |
|
|
|
Crito
Starting Member
40 Posts |
Posted - 2005-03-19 : 05:00:11
|
| @@FETCH_STATUS works with nested cursors just fine. When you come out of the lower level loop, very next statement is a fetch, so when @@FETCH_STATUS is evaluated again it'll have the proper value for the nest level its at. |
|
|
|
t1g312
Posting Yak Master
148 Posts |
Posted - 2005-03-19 : 23:42:12
|
| Thanks guys! BTW rob, what is with you and cursors??? jk...Adi-------------------------/me sux @sql server |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-20 : 07:44:03
|
I was in love, and in business, with a cursor once. She dumped me and took all my money. Bitch. So that's why I always say "cursors are a bitch".  OK, I've never said that before. But it's true anyway. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-03-20 : 10:46:16
|
| Too many men realize too late that you just can't keep asking them to Fetch this, Fetch that. Pretty soon they get fed up and leave you with a Fetch_Status of a big fat lonely zero.Be One with the OptimizerTG |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-03-21 : 10:19:55
|
| And who was it that said this?GetDate() Still leaves you alone on a Saturday night....then there was the BULK INSERT....but we won't go thereBrett8-) |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-03-21 : 10:24:16
|
I'm not into the Buld Bulk Insert scene. I just hope I never have to worry about a RaiseError...Be One with the OptimizerTG |
|
|
|
|
Nested Cursor