Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2013
    Posts
    46

    Unanswered: Cursor not working correctly.

    Hi Friends,

    I am tring to send an email reminder to multiple users using a cursor.

    declare emailCursor cursor for
    select name,info,default_email, @MyMessage
    from My_Temp

    open emailCursor
    fetch next from emailCursor into @Name,@info, @Recipients,@MyMessage

    while(@@FETCH_STATUS = 0)
    begin

    -- select name,info,ins_default_email,@MyMessage
    --from temp

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'Hello',
    @recipients = @Recipients,
    @from_address = '<address.com>',
    @Body = @MyMessage,
    @subject = 'Subject'


    fetch next from emailCursor into @Name,@info, @Recipients,@MyMessage

    end
    close emailCursor
    deallocate emailCursor

    Currently I have two users in my table that I want to send email to, but when the stored procedure runs I got two emails for the last user in the table instead one for each user.

    Thank you for your help!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Post the results of this back please:
    Code:
    SELECT default_email
    FROM   My_Temp 
    
    DECLARE emailCursor CURSOR FOR
      SELECT default_email
      FROM   My_Temp 
    
    OPEN emailCursor
    
    FETCH NEXT FROM emailCursor
      INTO @Recipients
    
    WHILE (@@Fetch_Status = 0)
      BEGIN
        SELECT @Recipients
    
        FETCH NEXT FROM emailCursor
          INTO @Recipients
      END
    
    CLOSE emailCursor 
    DEALLOCATE emailCursor
    Extra notes: No need to have @MyMessage in the fetch as it is a static value throughout.
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2013
    Posts
    46
    Hi George,
    it works thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •