need help to send mail only if records exist

• neellotus07

  Ten Centuries

  Points: 1245

  More actions

  November 25, 2013 at 10:08 pm

  #285532

  Hi all,

  I am trying to sending a mail only if record exist as on current date so i am using sp_send_dbmail within a query as i mentioned below :-

  declare @cd varchar(10);

  declare @qry varchar(200);

  set @cd='LM004'

  set @qry='select(select case when sl_code=@cd THEN sl_code else null END as sl_code from test1 where sl_code=@cd and DATEDIFF(DAY,doc_dt,GETDATE())=0) as sl_code'

  IF @qry IS NOT NULL

  BEGIN

  EXEC msdb.dbo.sp_send_dbmail

  @profile_name = 'SQL Profile',

  @recipients = 'testmail@testserver.com',

  @subject = 'Inv Created'

  END

  but it is sending mail even @qry value is null.

  Pls tell me where i am doing wrong because i dont want to send mail when record is null or doesn't exist. I will be very thankful to you.

  Thanx

  neel

• Koen Verbeeck

  SSC Guru

  Points: 259155

  More actions

  November 26, 2013 at 12:17 am

  #1669036

  You never ever execute the code inside @qry.

  You just store some string in it (which happens to be a SQL statement, but that doesn't matter).

  Then you check if @qry is NULL or not. Of course it is not null, you just stored a string in it.

  Just execute the SQL statement directly and store the result in a variable.

  edit: regarding the SQL statement: why are you using a nested subquery?

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• Lowell

  SSC Guru

  Points: 323518

  More actions

  November 26, 2013 at 6:04 am

  #1669109

  test for the data with an exists:

  IF EXISTS( SELECT 1 from test1 WHERE sl_code=@cd AND DATEDIFF(DAY,doc_dt,GETDATE())=0) )

  BEGIN

  DECLARE @htmlbody VARCHAR(4000)

  SET @htmlbody = 'This is a Simple Email Example generated on ' + CONVERT( VARCHAR( 20 ), GETDATE(), 113 ) +

  ' to demonstrate sending a basic notification'

  --this assumes a profile was set as "default", so i don't have to explicitly specify which one to use. else you get this error:

  /*

  Msg 14636, Level 16, State 1, Procedure sp_send_dbmail, Line 112

  No global profile is configured. Specify a profile name in the @profile_name parameter.

  */

  EXEC msdb.dbo.sp_send_dbmail

  --@profile_name='SomeDomain DBMail',

  @recipients='lowell@somedomain.com',

  @subject = 'Simple Email Example',

  @body = @htmlbody,

  @body_format = 'HTML'

  --@body_format = 'TEXT'

  END --IF

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!