Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SQL Server
Expertise: Beginner
Jun 4, 2000

E-mail in a Query

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.

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 'name@emailaddress.com'
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] GO CREATE 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] GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE PROCEDURE usp_CheckForMail @SendToEmail varchar(255) AS DECLARE @ID int, @Fname varchar(50), @Lname varchar(50),
@EmailAddress varchar(100), @MsgText varchar(255) DECLARE WebVisitors CURSOR FOR SELECT ID, Lname, Fname, EmailAddress FROM TestTable WHERE SendMail = 'Y' ORDER BY Lname, Fname OPEN WebVisitors FETCH NEXT FROM WebVisitors INTO @ID, @Lname, @Fname, @EmailAddress EXEC 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 = 0 BEGIN -- 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, @EmailAddress END CLOSE WebVisitors DEALLOCATE WebVisitors GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO
DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date