devxlogo

E-mail in a Query

E-mail in a Query

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

See also  How HealthStream Learning Center Supports Healthcare Education and Compliance
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist