SQL Server : DBMail Stored Procedure -

i novice sql server, , have created stored procedure amalgamation of posts.

here stored procedure - works well, apart fact uses number of days first entry, , doesn't change subsequently, same @numberofdays each email.

 alter procedure [dbo].[renewalscheck]          declare @companyname nvarchar (50) = null         declare @productkey nvarchar (50) = null         declare @productname nvarchar (50) = null         declare @maintenancestartdate nvarchar (10) = null         declare @maintenanceenddate nvarchar (10) = null         declare @result nvarchar (10) = null         declare @emailed int = null         declare @sent nvarchar (10) = null         declare @body nvarchar (max) = null         declare @subject nvarchar (max) = null         declare @salesmanemail nvarchar (max) = null         declare @numberofdays nvarchar (5) = null         declare @followup int = null  declare cemail cursor local fast_forward  select   [companyname], [product key], [product name], [maintenance start date], [maintenance end date], [emailed], [salesmanemail], [followup ec emailed]  dbo.product   datediff(d,getdate(),[maintenance end date]) < 15 , [followup ec emailed]  = '0'  select @numberofdays = datediff(d,getdate(),[maintenance end date]) dbo.product datediff(d,getdate(),[maintenance end date]) < 15 , [followup ec emailed] = '0'  open cemail;  while 1 = 1 begin      fetch next cemail @companyname, @productkey, @productname, @maintenancestartdate ,@maintenanceenddate,     @emailed, @salesmanemail;      if @@fetch_status = -1 break;      set @body = 'the product ' + @productname + ' product key of ' + @productkey + ' expires in ' + @numberofdays + ' days.'     set @subject = 'product expiring in ' + @numberofdays + ' @ ' + @companyname      execute msdb.dbo.sp_send_dbmail @profile_name='jason',                                     @subject = @subject,                                     @recipients = @salesmanemail,                                     @copy_recipients ='',                                     @body = @body;  end      update [product]     set [followup ec emailed] = '1'     datediff(d,getdate(),[maintenance end date]) < 15 , [followup ec emailed] = '0'  close cemail; deallocate cemail; 

i know of question how it's setup , may have better ways of doing this, new, , working apart 1 tiny section.

any appreciated.

regards

jason

the problem number of days set once outside cursor loop

you can include in cursor query adding case field - like:

select  [companyname], [product key], [product name], [maintenance start date], [maintenance end date], [emailed], [salesmanemail], [followup ec emailed], case when datediff(d,getdate(),[maintenance end date]) < 15 , [followup ec emailed] = '0' datediff(d,getdate(),[maintenance end date]) end numberdays dbo.product 

you need add assignment @numberofdays fetch next