SQL Server CE: Alternative to ISNULL(): CASE... END

[Jon Pawley]

Hi,

This isn't a question, but just a piece of information which might be
useful to others. I'm posting it in the hope that it might be easier
for them to find than it was for me.

I had a SQL Server 2000 SQL statement that looked like this:

SELECT
ISNULL(VehicleMake, '') + ' ' +
ISNULL(VehicleModel, '') AS VehicleDescription
FROM
Vehicles

When I moved this across to SQL Server CE it plain didn't work, and I
got the error:

Error: 0x80040e14 DB_E_ERRORSINCOMMAND
Native Error: (25946)
Description: The number of arguments specified for function
<func_name> is not correct. [,,,,,]
Interface defining error: IID_ICommand
Param. 0: 1
Param. 1: 1
Param. 2: 0
Param. 3: ISNULL
Param. 4:
Param. 5:

A bit of research showed me that SQL Server CE does not implement the
ISNULL() function. (You can look for
yourself--http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlce/htm/_lce_functions.asp
does not list ISNULL())

I dabbled with the IS [NOT] NULL expression but couldn't get it to do
what I wanted. I wasn't using it correctly.

Then I came across a Usenet posting earlier in this group that
explained how to achieve the desired result using the CASE... END
construct. It's a little convoluted, but it works:

SELECT
CASE VehicleMake IS NULL THEN '' ELSE VehicleMake END
+ ' ' +
CASE VehicleModel IS NULL THEN '' ELSE VehicleModel END
AS VehicleDescription
FROM
Vehicles

So, there you go. If you need to reproduce the ISNULL() function from
SQL Server 2000 then use the CASE... END construct, as shown above.

Hope that helps,

Jon