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]

Go to Top of Page

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
Go to Top of Page
   

- Advertisement -