Send alert email from database using CURSOR.

--— =============================================

--— Author: S K

--— Create date: Date

--— Description: Desc

--— =============================================

CREATE PROC Sp_AlertMail

AS

declare @EmpId varchar(50)

Declare @Name varchar(50)

Declare @Email varchar(50)

Declare @Moblie varchar(50)

Declare Mail_Alert Cursor For

SELECT EmpId,Name,Email,Moblie FROM Employee

WHERE  DATEDIFF(DAY,CONVERT(DATETIME,CONVERT(DATE,GETDATE())),CreatedDate) IN(1,2)

OPEN Mail_Alert

FETCH NEXT FROM  Mail_Alert Into @EmpId,@Name,@Email,@Service,@Moblie

WHILE(@@fetch_status=0)

BEGIN

begin

Declare @Body varchar(max)

SET @Body = 'your text….'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Alert_From_Database',

@recipients=@EMAIL,

@copy_recipients ='abc@gmail.com' ,

@subject = 'Alert Mail',

@body = @Body,

@body_format = 'HTML';

End

FETCH  Mail_Alert Into @EmpId,@Name,@Email,@Service,@Moblie

END

CLOSE Mail_Alert

DEALLOCATE Mail_Alert