ISNULL for MsSql Server Ce

Write your "How To" regarding anything you are comfortable with and you feel it will help the forum members.

NOTE :: All threads started here will appear only after the approval from Administrator
Post Reply
thetarget
Posts: 496
Joined: Sat Jul 09, 2005 9:10 am

ISNULL for MsSql Server Ce

Post by thetarget »

Hey guys

this might be helpful for some.

As some .NET developers here may notice, there are no ISNULL() function for SQL Server CE. However, there is another function is very similar.

function name: coalesce

Usage:

User
- - - - - - - -
| Name | ID |
| NULL | 1 |
| john | 2 |
-------------

#1:
Select all the name in the user table, replacing NULL with 'no name specified'
SELECT COALESCE(Name,'no name specified') AS Name FROM user

In this example, I selected the 'Name' column from User table. Since it has NULL value inside, it is better to replace it with some more meaningful strings.

#2:
Auto create ID, IN CASE the table does not have auto increment specified
INSERT INTO User(ID) (SELECT COALESCE(MAX(ID),0)+1 FROM User)

In this example, there are a few parts:
INSERT INTO User(ID) ...
This is straight forward, a normal insert statement

(SELECT COALESCE(MAX(ID),0)+1 FROM User)
this is a sub query, to provide the automatic generated ID.
MAX(ID) will get the largest value in the table in column 'ID', in this case, it is 2
If table User has 0 records, Max(ID) will return a NULL value, thus, COALESCE() is used to replace the NULL value to a more usable value.


hope this helps.. :)


SHAdmin
Posts: 2089
Joined: Sat Dec 18, 2004 11:28 am
Contact:

Post by SHAdmin »

Bet that will help the needy :)

Your account has been credited with 20 points for sharing it.
Post Reply