IF NOT - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

IF NOT

Author

Topic

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2013-05-24 : 17:51:56

Hi

I'd like to use an IF statement, but when a value is anything but. For example:

IF @MyIntVar <> 0
BEGIN

I'm new to SP's and <> doesn't seem to work?

Thanks as always

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-05-24 : 17:58:03

What value is @MyIntVar? If it is NULL then it will evaluate to UNKNOWN.

Maybe this?:

IF @MyIntVar <> 0 OR @MyVar IS NULL
BEGIN

-- or

IF COALESCE(@MyIntVar, 1) <> 0
BEGIN

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2013-05-24 : 18:21:29

Thanks. I didn't think <> worked with SP's. When I saw your example, I soon realised it was an issue with my code.

Thanks for your help

quote:
Originally posted by Lamprey

What value is @MyIntVar? If it is NULL then it will evaluate to UNKNOWN.

Maybe this?:
IF @MyIntVar <> 0 OR @MyVar IS NULL
BEGIN

-- or

IF COALESCE(@MyIntVar, 1) <> 0
BEGIN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-25 : 09:27:11

its not about issue with <> in SPs but its because <> doesn't work with NULL values. NULL is not stored as a value but it just represents a condition that value is unknown (set as a internal bit). So no operators like =,>=,<=,<> etc will not work with NULL. NULL comparisons should be done using IS NULL or IS NOT NULL checks under default conditions (based on ANSI NULL settings)

------------------------------------------------------------------------------------------------------
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