none
how to send mail for case statement Success

    Question

  • Hi,

    I have a case statement, which success should send an email and failed statement should send Failed status email.

    I am unable to execute EXEC statement within the Case statement. Please help how can I send mail from case statement.

    Regards,

    Krishna Kumar.

    Saturday, October 19, 2013 4:37 AM

Answers

  • Sorry, Please try the below:

    BTB, you need a common column to join these two tables. Please provide you table structures for these two table, weould be able to help better.

    If exists (Select 1 from totalspace ts, AvailableSpace asp where ts.totalsize > asp.AvailableSize)

     print 'success'
    else print'failure'


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Monday, October 21, 2013 7:57 AM

All replies

  • Use an IF statement for doing the same. Read:

    http://technet.microsoft.com/en-us/library/ms182717.aspx


    Many Thanks & Best Regards, Hua Min

    Saturday, October 19, 2013 4:56 AM
  • Hi ,

    Try this thread - http://social.msdn.microsoft.com/Forums/sqlserver/en-US/0b2f4dc8-c83c-4188-96db-4570560fc75c/email-from-mssql-if-some-condition-is-true?forum=transactsql

    Based on condition (IF..ELSE logic) build the message and then pass the message to sp_send_dbmail .

    Kindly post your exact problem statement & error description , so that you will get appropriate help asap .


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Saturday, October 19, 2013 4:57 AM
  • My problem is , I have two columns from two different tables 

    table 1: TotalSpace - column : Totalsize

    table 2: AvailableSpace - column: AvailableSize

    I need to compare the value of the above two columns, say If Availablesize is greater or equal to Totalsize, it should send a mail that Availablesize is greater than Totalsize

    If Availablesize is lesser than totalsize it should execute the email proc and also run a job failing step or raiserror query. (since i am going to implement this as a job)

    my view point is:

    select case when (asp.AvailableSize > ts.totalsize) then 'exec.mail sending proc'

    else 'exec job failure proc and send mail' end from TotalSpace ts, Availablespace asp;

    Correct me if i am wrong and guide me towards solution


    Monday, October 21, 2013 7:00 AM
  • With limitted knowledge on your requirement, May be like below:

    If(Select 1 From Tablename where AvailableSize > totalsize) 

         send mail

    else

       job failure proc and send mail


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, October 21, 2013 7:06 AM
  • I tried, like below (used print statement for testing) : 

    If (Select 1 from totalspace ts, AvailableSpace asp where ts.totalsize > asp.AvailableSize)

     print 'success'
    else print'failure'

    I am getting the error : 

    Msg 4145, Level 15, State 1, Line 57
    An expression of non-boolean type specified in a context where a condition is expected, near 'print'.

    Monday, October 21, 2013 7:34 AM
  • Looks like syntax miss, add exists after IF

    If exists (Select 1 from totalspace ts, AvailableSpace asp where ts.totalsize > asp.AvailableSize)
     print 'success'
    else print'failure'


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, October 21, 2013 7:56 AM
  • Sorry, Please try the below:

    BTB, you need a common column to join these two tables. Please provide you table structures for these two table, weould be able to help better.

    If exists (Select 1 from totalspace ts, AvailableSpace asp where ts.totalsize > asp.AvailableSize)

     print 'success'
    else print'failure'


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Monday, October 21, 2013 7:57 AM
  • Thanks a lot Latheesh, It worked out for me.
    Monday, October 21, 2013 11:43 AM