declare
@out_desc nvarchar(max),
@out_mesg nvarchar(50)
declare @ref nvarchar(50),
@next date
declare c1 cursor read_only
for
select [calibration_no], [description], [next_calibration]
from calibration.dbo.item
open c1
fetch next from c1 into
@ref, @next
while @@fetch_status = 0
begin
if datepart(month,@next) <= dateadd(month, 1, (datepart(month, getdate())))
begin
exec calibration.dbo.spSendMail @recipients = 'xxxxxxxx',
@subject = 'Test Email',
@from = 'xxxxxxxx',
@body = 'Item number ' + STR(@ref) + 'is due calibration on ' + STR(convert(nvarchar(20), @next))
+ '. <br><br>Please take the necessary action to ensure the item is calibrated before this time.
<br><br>You can view the item here > http://server/app/ViewItem.aspx?calibration_no=' + STR(@ref)
@output_mesg = @out_mesg output,
@output_desc = @out_desc output
print @out_mesg
print @out_desc
end
fetch next from c1 into
@ref, @next
end
close c1
deallocate c1