Check for duplicate and send email of duplicates to End User

Question

Check for duplicate and send email of duplicates to End User

SQL_Kills

Hall of Fame

Points: 3154
More actions
October 27, 2016 at 3:11 pm

#331602

Hi,
I have the below table
CREATE TABLE [dbo].[BookOrderTest](
[TitleID] [int] NULL,
[Title] [nvarchar](100) NULL,
[Quantity] [int] NULL
) ON [PRIMARY]
insert into bookordertest values (1,'The Catcher in the Rye',3)
insert into bookordertest values (1,'The Catcher in the Rye',3)
insert into bookordertest values (3,'The Great Gatsby',0)
insert into bookordertest values (4,'Gone with the Wind',4)
insert into bookordertest values (5,'Jane Eyre',5)
insert into bookordertest values (7,'Age of Innocence',8)
Select titleid, COUNT(*) cnt
FROM BookOrderTest
Group By titleid
having COUNT(*)>1
I want to send a email to Joe.Bloggs@google.com if there are duplicates returning and to advise which titleid they are. If there are no duplicates then no email to be sent.
Any ideas?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

SQL_Kills (10/27/2016)
Hi,
I have the below table
CREATE TABLE [dbo].[BookOrderTest](
[TitleID] [int] NULL,
[Title] [nvarchar](100) NULL,
[Quantity] [int] NULL
) ON [PRIMARY]
insert into bookordertest values (1,'The Catcher in the Rye',3)
insert into bookordertest values (1,'The Catcher in the Rye',3)
insert into bookordertest values (3,'The Great Gatsby',0)
insert into bookordertest values (4,'Gone with the Wind',4)
insert into bookordertest values (5,'Jane Eyre',5)
insert into bookordertest values (7,'Age of Innocence',8)
Select titleid, COUNT(*) cnt
FROM BookOrderTest
Group By titleid
having COUNT(*)>1
I want to send a email to Joe.Bloggs@google.com if there are duplicates returning and to advise which titleid they are. If there are no duplicates then no email to be sent.
Any ideas?

Do you have database mail set up on your server and have you ever used sp_Send_DBMail before?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL_Kills Hall of Fame Points: 3154 More actions · Answer 2

I think I have seen it somewhere but I'm not sure how to use the PRINT to displace the sublimate values on the BODY of the email?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

SQL_Kills (10/27/2016)
I think I have seen it somewhere but I'm not sure how to use the PRINT to displace the sublimate values on the BODY of the email?

Yabingooglehoo helps here a lot. See the following URL.

https://msdn.microsoft.com/en-us/library/ms190307.aspx

Example "B" is similar to solving your problem as I did in the code below. You can make things much more pretty by taking a look at example "C".

Here's the simple code:

SELECT TitleID, COUNT(*) cnt

INTO ##Results

FROM dbo.BookOrderTest

GROUP BY TitleID

HAVING COUNT(*)>1

;

IF @@ROWCOUNT > 0

EXEC msdb.dbo.sp_send_dbmail

@recipients = 'put email address(es) here',

@query = 'SET NOCOUNT ON; SELECT * FROM ##Results' ,

@subject = 'Duplicate Book Orders',

@body = 'Put any amplifying information you want to send here.'

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)