E-mail in a Query

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 '[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 [email protected], @subject = 'New Web Visitor' FETCH NEXT FROM WebVisitors INTO @ID, @Lname, @Fname, @EmailAddressENDCLOSE WebVisitorsDEALLOCATE WebVisitorsGOSET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO

Share the Post:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several