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

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.

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



Application Security Testing: An Integral Part of DevOps

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