February 16, 2010 at 6:52 pm
I was looking around some of my colleague's code and found an interesting difference in how we both handled isNull. So my question is, Is there much if any difference between doing one way or the other. Code Examples below.
My Way
IF @Var1 Is Null
exec someSP.
ELSE
Exec SomeOtherSP
OR
IF Not @Var1 is Null
Exec SomeOtherSP
ELSE
Exec SomeSP
My Colleagues Way
If isNull(@Var1, 'someValue') = 'SomeValue'
Exec SomeSP
ELSE
Exec SomeOtherSP
'SomeValue' sometimes is an integer like 0 or 1, other times he has used 'NULLZ'
Thanks in advance for any incite.
February 16, 2010 at 9:32 pm
There's a rather distinct difference.
If you were to "translate" your colleague's way to your notation, it would be:
IF @Var1 Is Null OR @var1='SomeValue'
exec someSP
ELSE
Exec SomeOtherSP
That's essentially an entirely different possible criterion when that someSP might be executed. It may be that "SomeValue" would not occur (i.e. it's not a valid option), in which case it might never come up as a problem, but otherwise, the two syntaxes are different in meaning.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 17, 2010 at 4:48 am
IS NULL and IS NOT NULL are provided to facilitate comparisons with NULL. Using a function like ISNULL(X, Y) instead is a bit of an odd choice - and error-prone as Matt points out.
Quite aside from the oddness of not using the intended language feature, the problem is that you have to choose a 'special value' to replace the NULL in the comparison. Your colleague's code depends on the replacement value (0, 1, or "NULLZ") not being a valid value for the variable or column being tested.
In short, there is nothing good about using the ISNULL function in this way.
Notice the construct 'IS NOT NULL' rather than NOT IS NULL. It is more normal to write:
IF @Var IS NOT NULL ...rather than... IF NOT @Var IS NULL
To see another reason to avoid your colleagues approach, try running the following:
DECLARE @Var VARCHAR (4);
SET @Var = NULL;
IF @Var IS NULL
BEGIN
PRINT '@Var is definitely NULL!'
END;
IF ISNULL(@Var, 'NULLZ') = 'NULLZ'
BEGIN
PRINT 'Why doesn''t this appear?'
END;
Paul
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply