Friday, May 15, 2009

Sql mail (2005 )send using stored procedure

ALTER PROCEDURE [dbo].[uspGenerateMailTimeSheetDailyReminder]
AS
--Declaration area
DECLARE @firstName VARCHAR(50)
DECLARE @lastName VARCHAR(50)
DECLARE @email VARCHAR(255)
declare @body1 varchar(500)

------Send mail To Employees end of the day for timesheet tasks
DECLARE Mailing CURSOR FOR


SELECT Email, EmployeeFirstName,EmployeeSurname
FROM UserGroup INNER JOIN
tblEmployees ON UserGroup.UserName = tblEmployees.UserName

WHERE UserGroup.GroupName='Employee' AND IsActive='1' AND tblEmployees.EmployeeFirstName='sanjeewa'



OPEN Mailing
FETCH NEXT FROM Mailing INTO @email, @firstName,@lastName
WHILE 0 = @@FETCH_STATUS
BEGIN

set @body1 = 'Dear ' + CAST(@firstName AS VARCHAR) + ' ' + CAST(@lastName AS VARCHAR)+',< br/>' +'< br/ >'+ 'Please update your time sheets before you sign out. ' +'< br/>'+'< br/ >' + 'Regards' + '< br/ >'+ 'Admin.'
EXEC msdb.dbo.sp_send_dbmail @recipients=@email,
@subject = 'Please update your time sheets. ',
@body = @body1,
@body_format = 'HTML' ;

FETCH NEXT FROM Mailing INTO @email, @firstName,@lastName
END

CLOSE Mailing
DEALLOCATE Mailing

No comments: