Using ISNULL

By Bill Graziano on 25 August 2000 | 5 Comments | Tags: Queries, Functions


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.

The full text of the question is "Say I have a table with the following fields below:

TRANSACTIONS
Txn_ID int AutoIncrement
Txn_Insert_Date datetime NOT NULL
Txn_Completed_Date datetime NULL


I want to do something similar to this:

Select
Txn_Insert_Date
From
TRANSACTIONS
Order By
Txn_Insert_Date;

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


SQL Server has a handy little function called ISNULL. It checks if a value is NULL (hence the clever name) and allows you to return a different value if it is. You might use it just like this:

SELECT ISNULL(au_id, 'XXX-XX-XXX' ) AS ssn
FROM authors


The ISNULL function compares au_id to null. If it is not null, it just returns the value of au_id. If it is null it will return XXX-XX-XXX. You can use column names, constants or variables in either of these parameters. You can also use it in computing sums or averages. For example,

SELECT AVG(ISNULL(price, $0.00))
FROM titles


This will set the value for the column price to $0.00 before computing the average. Knowing all this, you can write your query like this:

Select Isnull(Txn_Completed_Date, Txn_Insert_Date ) As TranDate
From TRANSACTIONS
Order By Txn_Completed_Date, Txn_Insert_Date


Note: Looking back over this (after I wrote my answer of course), it seems you might be using Access. I don't know the comparable function for ISNULL in Access. It shouldn't be to hard to find though.

Discuss this article: 5 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

Using Dynamic SQL in Stored Procedures (7 March 2011)

Using REPLACE in an UPDATE statement (31 March 2010)

Joining to the Next Sequential Row (2 April 2008)

Writing Outer Joins in T-SQL (11 February 2008)

Aggregating Correlated Sub-Queries (23 October 2007)

How to Use GROUP BY with Distinct Aggregates and Derived tables (31 July 2007)

How to Use GROUP BY in SQL Server (30 July 2007)

Returning Complex Data from User-Defined Functions with CROSS APPLY (11 June 2007)

Other Recent Forum Posts

How to add a dropdown to a texbox inside a report. (4 Replies)

C# code to put a dropdown list to excel (0 Replies)

Update Date based on Employee (4 Replies)

need help on Covering Indexes (0 Replies)

Repeat records depending field value (3 Replies)

Randomly Assign to Group (2 Replies)

Dates from weeknumber (3 Replies)

Transforming rows to Columns with PIVOT (2 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -