Stored Procedure Syntax

Question

Stored Procedure Syntax

Roger Abram

Hall of Fame

Points: 3683
More actions
January 5, 2010 at 9:44 am

#218084

I'm building a stored procedure where I want it to evaluate variables and then take action based on what the variables are.
Currently, I want it to look at the value in the @PAID_THRU date variable AND also check to see if it DOESN'T FIND the Record_ID in the trans table (using the @ID variable)
It just doesn't seem to be working. Do I have the syntax correct for looking at @Paid_Thru and also looking for the existence of @ID in a table?
IF @PAID_THRU < GETDATE() AND NOT EXISTS
(select Record_ID from trans t
where t.product_code = 'MBR' AND Record_ID = @ID)
BEGIN
--- do this that and the other
END
Thank you.
Rog

Viewing 8 posts - 1 through 7 (of 7 total)

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

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

Looks ok. What exactly do you mean by 'It just doesn't seem to be working.'?

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

So what is the problem? Are getting an error message?

Roger Abram Hall of Fame Points: 3683 More actions · Answer 3

It doesn't return an error message when it runs... just doesn't evaluate correctly.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

What happens if you change the

IF @PAID_THRU < GETDATE() AND NOT EXISTS

to

IF @PAID_THRU <= GETDATE() AND NOT EXISTS

Also, have you thrown any print statements into it to see what it is evaluating?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

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

Have you tested the statements separately? Have you used print to check the values of the date variable? Have you done selects to ensure that there really are (or are not) rows?

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

Roger Abram Hall of Fame Points: 3683 More actions · Answer 6

I put it aside for several hours and came back with fresh eyes and found I had some bad logic elsewhere that was causing my erratic results.

Thanks everyone!

Roger

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Thanks for the update.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events