need help to send mail on insertion of records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

need help to send mail on insertion of records

Author

Topic

neellotus
Starting Member

15 Posts

Posted - 2013-11-26 : 00:01:12

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-26 : 01:04:43

you did not execute any query at all

What you did with set @qry = 'select. .. ' is only store a string of characters in the @qry variable

try changing to

select  @qry = 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)

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-26 : 05:36:08

As per what you gave @Qry will always be not null as you're storing the query itself in it and not the result of that.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources