Question:
How do I send an e-mail to myself if a field reads “SEND_MAIL?”
For example, a user wants more info on a product, so he clicks on the Send Mail button, which in turn throws an entry into the database. This entry is SEND_MAIL. Now at the end of the day I would like the server to scan the database for the word SEND_MAIL and, when it finds one, send me an email with the name and email address of the person.
Answer:
To answer this question I took the scenario of a Web site that logs user registrations and puts a visitor’s first name, last name and e-mail address in a table. I then created a stored procedure that selects this data and e-mails it to an e-mail address provided as an input parameter. If the SendMail column in the table is Y, then the details will be e-mailed. The stored procedure is called like this:
usp_CheckForMail '[email protected]'
The script to create the table is as follows:
if exists (select * from sysobjects where id =
object_id(N'[dbo].[TestTable]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[TestTable]GOCREATE TABLE [dbo].[TestTable] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [Fname] [varchar] (50) NULL , [Lname] [varchar] (50) NULL , [EmailAddress] [varchar] (100) NULL , [SendMail] [char] (1) NULL ) ON [PRIMARY]GO
The script to create the stored procedure to send the e-mails is:
if exists (select * from sysobjects where id =
object_id(N'[dbo].[usp_CheckForMail]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[usp_CheckForMail]GOSET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GOCREATE PROCEDURE usp_CheckForMail @SendToEmail varchar(255)ASDECLARE @ID int, @Fname varchar(50), @Lname varchar(50),
@EmailAddress varchar(100), @MsgText varchar(255)DECLARE WebVisitors CURSOR FORSELECT ID, Lname, Fname, EmailAddress FROM TestTableWHERE SendMail = 'Y'ORDER BY Lname, FnameOPEN WebVisitorsFETCH NEXT FROM WebVisitorsINTO @ID, @Lname, @Fname, @EmailAddressEXEC master.dbo.xp_startmail /* typically mail will
be started on your server */-- Check @@FETCH_STATUS to see if there are any more
rows to fetch.WHILE @@FETCH_STATUS = 0BEGIN-- PRINT 'Visitor: ' + @Fname + ' ' + @Lname + ' at '
+ @EmailAddress + ' [ID=' + CAST(@ID AS varchar) + ']' SET @MsgText = 'Visitor: ' + @Fname + ' ' + @Lname + ' at '
+ @EmailAddress + ' [ID=' + CAST(@ID AS varchar) + ']' EXEC master.dbo.xp_sendmail @recipients = @SendToEmail,
@message =@MsgText, @subject = 'New Web Visitor' FETCH NEXT FROM WebVisitors INTO @ID, @Lname, @Fname, @EmailAddressENDCLOSE WebVisitorsDEALLOCATE WebVisitorsGOSET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO
