April 13, 2009 at 9:45 am
Hi
I am so stumped (and new to really using sql server).
Here's what I'm trying to do. I'm loading a temp table with email addresses from a view.
Then I'd like to loop through the temp table:
1) Grab the email address
2) Use [msdb] to send an email to that person (with an attached query)
The view works fine, the results are fine, the temp table has the correct # of rows & email addresses, but I have no idea how to loop the table
Here's what I've got:
[Code]Use [UpdatedOnCall]
Go
Create Table #EmailAddresses (SendTo varchar(255) Null)
Go
Declare @MyDate varchar(11)
Declare @EmailAddress varchar(25)
Set @MyDate = (Select convert(datetime,convert( varchar(11), dateadd(d,4,getdate()),101)))
Declare Email_Cursor Cursor For Select LoweredEmail From v_OnCallDatesWithNameAndEmail Where Starts = @MyDate
Open Email_Cursor
Fetch Next From Email_Cursor Into @EmailAddress
While @@Fetch_Status = 0
Begin
Print 'Email Address: ' + @EmailAddress
Insert #EmailAddresses(SendTo) Select @EmailAddress
Fetch Next From Email_Cursor Into @EmailAddress
End
Close Email_Cursor
Deallocate Email_Cursor
Go
Declare @MyCount int
Set @MyCount = (Select Count(SendTo) From #EmailAddresses)
print Cast(@MyCount As varchar(25))
Go
/***************************
Loop
***************************/
Drop table #EmailAddresses[/Code]
Thank you!
April 13, 2009 at 12:17 pm
Hi Cliff,
I would like to say that it would be ideal to avoid a loop if at all possible, but without knowing the complexities of your email requirements, I will offer the following:
It looks like you already have everything you need, you just need to rearrange your logic. You have a loop(cursor) that is generating the data for your temp table. Rather than creating another cursor to loop through the records in your temp table, you could eliminate the temp table and perform the logic in the existing cursor:
[Code]Use [UpdatedOnCall]
Go
Declare @MyDate varchar(11)
Declare @EmailAddress varchar(25)
Set @MyDate = (Select convert(datetime,convert( varchar(11), dateadd(d,4,getdate()),101)))
Declare Email_Cursor Cursor For Select LoweredEmail From v_OnCallDatesWithNameAndEmail Where Starts = @MyDate
Open Email_Cursor
Fetch Next From Email_Cursor Into @EmailAddress
While @@Fetch_Status = 0
Begin
Print 'Email Address: ' + @EmailAddress
/***************************
Email Logic Here
***************************/
Fetch Next From Email_Cursor Into @EmailAddress
End
Close Email_Cursor
Deallocate Email_Cursor
Go
[/Code]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply