MAIL

create proc usp_expdate_sendmail
as
begin
declare @exp_date varchar(50)
declare @mail_id varchar(50)
declare @body varchar(max)
declare @veh_type varchar(50)
 
declare @name varchar(20)
declare @str varchar(max)
declare c1 cursor for
select mail_id,exp_date from  tbl_insurance where exp_date between getdate() and dateadd(day,30,getdate());
open c1
fetch next from c1 into @exp_date,@mail_id
while(@@FETCH_STATUS=0)
begin
set @body='ur vehicle insurence is expires on :' +@exp_date;
exec msdb.dbo.sp_send_dbmail @profile_name='bhavani',@recipients=@mail_id,
@body=@body
 
fetch next from c1 into @exp_date,@mail_id
end
 
close c1
deallocate c1
 end