Post reply

How to send email from procedure depend on value

  • June 6, 2016 at 6:23 pm

    #311045

    Hello Good Morning,

    I have the below procedure if it returns 0 no need to send any email , if it is more than 0 then we need to send email using

    EXEC msdb.dbo.sp_send_dbmail (feel free to change the procedure, we don't need any parameters I just used it)

    ---Procedure

    Create PROCEDURE [dbo].[msp_CpgnRgPhoneAppendVerify]

    AS

    SET NOCOUNT ON

    DECLARE @DataTable int, @FlagTable int,@result int

    -- Query 1 to Check the phone Append process Tables

    SELECT @DataTable = COUNT(*) FROM Oscar_prod.dbo.cpgnrgheader (nolock)

    where Phoneappend ='Y'

    AND InboundMatch is null

    AND Wave_Date < CONVERT(Date, GETDATE ())

    AND Wave_Date > CONVERT(Date, GETDATE ()-3)

    -- Query 2 to Check the phone Append Flag

    SELECT @FlagTable =

    CASE WHEN InProcess=0 THEN 0

    ELSE DATEDIFF (HH,processdate,GETDATE())

    END

    FROM oscar_prod.dbo.CpgnRgPhoneProcessFlag (NOLOCK)

    SET @result = ISNULL(@datatable,0) +ISNULL(@flagtable,0)

    return @result

    SET NOCOUNT OFF

    --Execution Code

    DECLARE @valback INT;

    EXEC @valback = dbo.msp_CpgnRgPhoneAppendVerify;

    SELECT @valback

    please help me how to send email (add code EXEC msdb.dbo.sp_send_dbmail ) to send email

    Thank you much in advance

    Milan

  • June 6, 2016 at 8:25 pm

    #1883491

    Can any one please help me

  • June 6, 2016 at 8:31 pm

    #1883493

    asita (6/6/2016)

    Hello Good Morning,

    I have the below procedure if it returns 0 no need to send any email , if it is more than 0 then we need to send email using

    EXEC msdb.dbo.sp_send_dbmail (feel free to change the procedure, we don't need any parameters I just used it)

    {snip}

    please help me how to send email (add code EXEC msdb.dbo.sp_send_dbmail ) to send email

    Your requirement pretty much describes the code you need to do it. Use an IF block and if it meets the requirements, have the TRUE part of the IF block do an EXEC on sp_send_dbmail with the correct address, subject, and body content.

    Please Google sp_Send_DBMail for the details of the call. You can do this. 😉

    --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 3 posts - 1 through 2 (of 2 total)

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