
/****** Object:  StoredProcedure [dbo].[sp_send_email_mobile]    Script Date: 07/11/2024 5:32:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE procedure [dbo].[sp_send_email_mobile]
	
AS
BEGIN

DECLARE @as_mail_profile	VARCHAR(200) ; 
DECLARE @as_to				VARCHAR(1000) ;
DECLARE @as_subject			VARCHAR(255) ;
DECLARE @as_body			VARCHAR(MAX);
DECLARE @an_rowid			NUMERIC(14,0);

DECLARE EMAIL_CR CURSOR FAST_FORWARD FOR

	SELECT	eml_log_from,
			eml_log_to_email,
			eml_log_subject,
			eml_log_content,
			rowid
	FROM	eml_log (NOLOCK)
	WHERE	eml_log_status IN ('P', 'E') 
	ORDER BY RowID
	
	OPEN EMAIL_CR 
		FETCH NEXT FROM EMAIL_CR Into @as_mail_profile, @as_to, @as_subject, @as_body, @an_rowid ;	
		WHILE @@FETCH_STATUS = 0 
		BEGIN
		
			EXEC msdb.dbo.sp_send_dbmail  
			@profile_name = 'CMMS Admin',  
			@recipients = @as_to,  
			@subject = @as_subject,  
			@body = @as_body ,
			@body_format = 'HTML'; 
		
			UPDATE	eml_log
			SET		eml_log_status = 'S',
					eml_log_msg = 'The mail had been successfully sent.'
			WHERE	RowID = @an_rowid ;	
			
		FETCH NEXT FROM EMAIL_CR Into @as_mail_profile, @as_to, @as_subject, @as_body, @an_rowid ;
		END
	
	CLOSE EMAIL_CR 
	DEALLOCATE EMAIL_CR 
END

