Update table

• mtz676

  SSCrazy Eights

  Points: 9212

  More actions

  January 11, 2021 at 4:36 pm

  #3830976

  DECLARE @Name varchar(80)
  DECLARE @InsuranceReminderDate datetime
  DECLARE @body varchar(150)
  DECLARE @Email varchar(100)
  
  
  DECLARE Messaging CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
  
  SELECT distinct C.Name,I.InsuranceReminderDate,C.Email
  from [dbo].[Customer] C 
                  inner join [dbo].[Insurance] I
  on C.ID=I.ID
  where convert(date,[I.InsuranceReminderDate]) = CONVERT(date, getdate())
  I.MsgSent<>1
  
  OPEN Messaging;
  WHILE 1=1
  BEGIN
  FETCH NEXT FROM Messaging INTO @Name,@InsuranceReminderDate,@Email;
  IF @@FETCH_STATUS = -1 BREAK;
  
  SET @body = @Name +', please renew '+ 'today'
  EXEC msdb.dbo.sp_send_dbmail 
  @recipients = @Email,
  @subject = 'Insurance Reminder',
  @body = @body
  END
  CLOSE Messaging;
  DEALLOCATE Messaging;
  RETURN;
  GO
  
  The above code runs 4 times a day.
  Sometimes new rows can get added to the Insurance table for the current date in which case only those rows should be getting an reminder email.
  The Insurance table has a column [MsgSent] which needs to be flagged as 1 once an email is sent.
  
  Ex:
  Insurance table(listing only few columns here)
  Name Insurance InsuranceReminderDate MsgSent etc...
  A    1000      1/11/2021               0
  B    1000      1/11/2021               0
  
  Once the above code runs say at 6:00 AM MsgSent should be updated to 1 for those rows.
  In case new rows get addded to this table with the current date 
  A    1000      1/11/2021               1
  B    1000      1/11/2021               1
  C    1000      1/11/2021               0
  If the code runs again at 10AM then ... 
  An email reminder should only be sent to C and the row C should be updated to 1 under MsgSent Column.
  How can I achieve this.
  

• Mike01

  SSChampion

  Points: 11395

  More actions

  January 11, 2021 at 5:13 pm

  #3831007

  What is the PK of the Insurance Table?  Include that in your cursor, then update the MsgSent to 1 after the send email proc call

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/