need help to send mail only if records exist

Question

Post reply

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

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

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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

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: 323504 More actions · Answer 2

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!