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
 Site Related Forums
 Article Discussion
 Article: Using ISNULL

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2000-08-25 : 02:40:37
Shane writes ". . . However, I want the returned value to be the value of Txn_Completed_Date if Txn_Completed_Date is not NULL.... Do you have any suggestions? I am currently doing it by pulling the data into a multi-dimensional array and then by doing the logic mentioned above and then by sorting it.... this works yet is very innefficient." Yikes. SQL Server can make this much easier.

Article Link.

skwilinski
Starting Member

1 Post

Posted - 2007-09-22 : 18:50:55
For those who are interested, in Access, it would be something like:

IIf(IsNull(COLUMN_VAL),0,COLUMN_VAL)

Thanks for the coalesce tip, it had slipped my mind.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-09-23 : 02:51:34
"Thanks for the coalesce tip, it had slipped my mind."

COALESCE() allows multiple choices:

COALESCE(Expression1, Expression2, Expression3, ...)

whereas IsNull only allows two.

And IsNull has peculiar behaviour on implicit casting, based on the first parameter I think, which can cause the value in the second parameter to be truncated etc.

And coupled with the confusing name I never use IsNull.

Kristen
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-23 : 10:24:44
quote:
Originally posted by skwilinski

For those who are interested, in Access, it would be something like:

IIf(IsNull(COLUMN_VAL),0,COLUMN_VAL)

Thanks for the coalesce tip, it had slipped my mind.



Access has a "NZ" function that is basically the same as IsNull() in SQL:

nz(COLUMN_VAL,0)



- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-24 : 01:42:44
quote:
Originally posted by Kristen

"Thanks for the coalesce tip, it had slipped my mind."

COALESCE() allows multiple choices:

COALESCE(Expression1, Expression2, Expression3, ...)

whereas IsNull only allows two.

And IsNull has peculiar behaviour on implicit casting, based on the first parameter I think, which can cause the value in the second parameter to be truncated etc.

And coupled with the confusing name I never use IsNull.

Kristen


Initially I thought ISNULL() should return 1 or 0 like other functions ISNUMERIC(), ISDATE(), etc until I see it in BOL that it is simplest verion of COALESCE and specific to SQL Server. It is confusing to have ISNULL in both front end and backend which behaves entirely different. I stopped using ISNULL at backend

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -