CODEWITHSUNDEEP
sqlsql-servert-sql
Soulrox
Soulrox

Reputation: 53

Cursor won't fetch 2nd Value

Hei folks,

I got a little problem with TSQL and a cursor. My cursor is suppose to loop through a list (view) of mailadresses. My Code goes like this: 

    set @sqlstatement = 'Declare cur CURSOR FOR SELECT ' + @col + ' 
    FROM ' + @view + ' group by ' + @col
    exec sp_executesql @sqlstatement

OPEN cur

FETCH NEXT FROM cur INTO @emailAddress
    WHILE @@FETCH_STATUS = 0 BEGIN

    SET @MyRecipients = @emailAddress;
    SET @MyResult = Replace(@MyResult,'defg',@emailAddress)
    SELECT @MyResult

        IF @@ROWCOUNT > 0
        begin   
        EXEC dbo.[Excel] @html = @html OUTPUT,@query = @MyResult

            IF @html != ''
            begin
                EXEC msdb.dbo.sp_send_dbmail
                @profile_name = 'Operator',
                @recipients= @MyRecipients, 
                @body = @html,
                @body_format = 'HTML',
                @subject= @MySubject,
                @copy_recipients = @MyCC
            ;                       
            END
        END 
        FETCH NEXT FROM cur INTO @emailAddress
    END
Close cur 
DEALLOCATE cur

So my problem is that the cursor does run 2 times if there are 2 mailadresses in the view, but I get 2 times the same mail instead of 2 different mails. (I'm part of the CC to see if it works)

Hope someone has some ideas and can help me.

Greetings,

Soul

Upvotes: 0

Views: 147

Answers (1)

Alankrit Agrawal
Alankrit Agrawal

Reputation: 109

I have gone through your problem and I have also executed the cursor used by you and it is working fine. As per my understanding following code could have created the problem.

SET @MyResult = Replace(@MyResult,'defg',@emailAddress)

I think it was not able to replace the email address of the previous iteration due to which the issue is occurring. It seems that here you want to replace the email address of the previous iteration with the email address of current iteration so following code might help.

SET @oldEmailAddress = 'defg'
set @sqlstatement = 'Declare cur CURSOR FOR SELECT ' + @col + ' 
FROM ' + @view + ' group by ' + @col
exec sp_executesql @sqlstatement

OPEN cur

FETCH NEXT FROM cur INTO @emailAddress
WHILE @@FETCH_STATUS = 0 BEGIN

SET @MyRecipients = @emailAddress;
SET @MyResult = Replace(@MyResult,@oldEmailAddress,@emailAddress)
SELECT @MyResult

    IF @@ROWCOUNT > 0
    begin   
    EXEC dbo.[Excel] @html = @html OUTPUT,@query = @MyResult

        IF @html != ''
        begin
            EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'Operator',
            @recipients= @MyRecipients, 
            @body = @html,
            @body_format = 'HTML',
            @subject= @MySubject,
            @copy_recipients = @MyCC
        ;                       
        END
    END 

    SET @oldEmailAddress = @emailAddress

    FETCH NEXT FROM cur INTO @emailAddress
END
Close cur 
DEALLOCATE cur

Upvotes: 1

Related Questions