Looping thru a temp table

  • 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!

  • 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