t-sql 2012 isnull

Question

Post reply

t-sql 2012 isnull

wendy elizabeth

SSCoach

Points: 16915
More actions
September 25, 2015 at 10:14 am

#308201

In a t-sql 2012 sql, I see the following in a where statement:
where ISNULL(I.Active, 0) = 1 AND ISNULL(AN.AccountNumber,0) IN (@Account).
Would you tell me what the ISNUll function is doing in the both isnull tests listed above?

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

"Give a man a fish and you feed him for a day; teach a man to fish and you feed him for a lifetime."

Here's the article on Books Online (BOL). To get there, just select the keyword and press F1 in SSMS.

https://msdn.microsoft.com/en-us/library/ms184325(v=sql.110).aspx

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jason A. Long SSC-Insane Points: 23790 More actions · Answer 2

wendy elizabeth (9/25/2015)
In a t-sql 2012 sql, I see the following in a where statement:
where ISNULL(I.Active, 0) = 1 AND ISNULL(AN.AccountNumber,0) IN (@Account).
Would you tell me what the ISNUll function is doing in the both isnull tests listed above?

Nothing other than preventing the effective use of an index.

Also, the "IN (@Account)" can be rewritten as "= @Account".

Edit... The "ISNULL(AN.AccountNumber,0) IN (@Account)." would allow nulls to be included if @Account = 0

A better way to write would be

AND (AN.AccountNumber = @Account OR (@Account = 0 AND AN.AccountNumber IS NULL))

OPTION(RECOMPILE)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Jason A. Long (9/25/2015)
wendy elizabeth (9/25/2015)
In a t-sql 2012 sql, I see the following in a where statement:
where ISNULL(I.Active, 0) = 1 AND ISNULL(AN.AccountNumber,0) IN (@Account).
Would you tell me what the ISNUll function is doing in the both isnull tests listed above?
Nothing other than preventing the effective use of an index.
Also, the "IN (@Account)" can be rewritten as "= @Account".

In fact, the ISNULL(I.Active, 0) = 1 can be written as I.Active = 1 with the exact same results.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Phil Parkin SSC Guru Points: 247180 More actions · Answer 4

Luis Cazares (9/25/2015)
"Give a man a fish and you feed him for a day; teach a man to fish and you feed him for a lifetime."
--

I like this version:

Give a man a fish and he will eat for a day. Teach him how to fish, and he will sit in a boat and drink beer all day.