MSSQLTips

http://www.mssqltips.com/tip.asp?tip=1599

One thing that should be added... if you're going to use a cursor and the results do not have to be sensitive to change and you don't need to move back and forth within the set, then look at using the FAST_FORWARD option which is a low overhead, read only, forward only type with no tempdb overhead.  It runs through the resultset sequentially.

I think another point to mention is to limit the amount of information obtained for use with the cursor.  I see a number of people who select everything from a table even though they only need two fields. 

 Ahhhh, yes. Good one, Tim.  I can't tell you how many times I've seen SELECT * used when declaring a cursor.

We are using a script very similar to this.  It uses exec msdb.dbo.sp_send_dbmail to email our clients appointment confirmations.  

A sql view has been written that calls the data set correctly.   Some clients have two appts the same day for different reasons and at different times. Most emails are sent correctly, with the correct data. 

But these records for clients that have two appts are sending only the data for one of the appts, and then sending it twice.  Any ideas?  Is there a flush command that can solve this?

Cheers,

Eric

Eric,

My first inclination would be that the query you use to populate the cursor is not getting the correct data.  If you just run the SELECT query in the 'Declare Cursor' portion of the code, are you getting the correct data? 

If not, this code needs to be amended.

If so, can you provide some sample data and the code?

Thank you,
The MSSQLTips Team