if else stored procedore

Question

Post reply

if else stored procedore

hbujar

SSC Enthusiast

Points: 138
More actions
January 28, 2010 at 5:53 am

#219990

Hi all,
I have one stored procedure that i want to compare one column, if that column e.g is rmo and it has the time where was inserted, i want if that is not inserted within three days to send me a email that there was no such a type inserted within these three days.
I have done one below but it don't work.
Below is the script where i have done.
DECLARE @eventype VARCHAR(50)
DECLARE @time DATETIME
DECLARE @alert VARCHAR(500)
SELECT @eventype = 'rmo'
FROM events e
WHERE e.eventtime > DATEADD(dd, -3, GETDATE())
AND e.eventtime < GETDATE();
SELECT @time = GETDATE()
IF @eventype !='rmo'
BEGIN
SET @alert = 'example@domain.com'
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'SQL Mail',
@recipients = @alert,
@subject = 'No eventtype' ,
@body = 'No such thing within last 3 days!'
END
If i say IF @eventype ='rmo' it sends email to me, which is not true.
I wnat to compare that there is no such a thing in that column.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

Nabha SSCrazy Eights Points: 8534 More actions · Answer 1

look at the line with bold font in the code below.

DECLARE @eventype VARCHAR(50)

DECLARE @time DATETIME

DECLARE @alert VARCHAR(500)

SELECT TOP 1 @eventype = rmo -- no quotes here and added top 1

FROM events e

WHERE e.eventtime > DATEADD(dd, -3, GETDATE())

AND e.eventtime < GETDATE();

SELECT @time = GETDATE()

IF @eventype !='rmo'

---------------------------------------------------------------------------------

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 2

Use the following and check

IF isnull(@eventype,'') !='rmo'

hbujar SSC Enthusiast Points: 138 More actions · Answer 3

Thank's for your response.

I changed the logic with count() function.

DECLARE @count int

SELECT @count = COUNT(*)

FROM events e

WHERE e.eventtime>DATEADD(dd ,-1 ,GETDATE())

AND e.eventtime<GETDATE()

AND e.eventType = 'hha';

IF @count=0

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

hbujar (1/28/2010)
DECLARE @count int
SELECT @count = COUNT(*)
FROM events e
WHERE e.eventtime>DATEADD(dd ,-1 ,GETDATE())
AND e.eventtime<GETDATE()
AND e.eventType = 'hha';
IF @count=0

Don't do that, it's highly inefficient to count up all the rows in the table to see if there are any. Use EXISTS or NOT EXISTS rather.

IF NOT EXISTS (

SELECT 1

FROM events e

WHERE e.eventtime>DATEADD(dd ,-1 ,GETDATE())

AND e.eventtime<GETDATE()

AND e.eventType = 'hha'

)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass