SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 IF NOT
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

richardlaw
Yak Posting Veteran

United Kingdom
68 Posts

Posted - 05/24/2013 :  17:51:56  Show Profile  Visit richardlaw's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 05/24/2013 :  17:58:03  Show Profile  Reply with Quote
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


Edited by - Lamprey on 05/24/2013 17:58:51
Go to Top of Page

richardlaw
Yak Posting Veteran

United Kingdom
68 Posts

Posted - 05/24/2013 :  18:21:29  Show Profile  Visit richardlaw's Homepage  Reply with Quote
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



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/25/2013 :  09:27:11  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000