Check for duplicate and send email of duplicates to End User

  • 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?

  • 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)

  • 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?

  • 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)

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

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