Detecting SQL Null values

[Guest]

When a sql column is read that contains a null, how is that detected. A sql
null value does not appear to be a system.dbnull.value. What data type is
used to detect the presence of a sql null.

Thanks,
Fred Herring

 

[Herfried K. Wagner [MVP]]

When a sql column is read that contains a null, how is that detected. A
sql
null value does not appear to be a system.dbnull.value. What data type is
used to detect the presence of a sql null.

Depending on the datatype, you can compare with 'Sql<datatype>.Null'.

 

[Guest]

Or get another solution from SQL query:
SELECT
CASE WHEN (Field1 IS NULL) THEN '' ELSE Field1 END AS Field1,
CASE WHEN (Field2 IS NULL) THEN 0 ELSE Field2 END AS Field2
FROM myTable
--Field1 data type is nvarchar
--Field2 data type is numeric
So "null value does not appear in your codes.

 

[Jim Hughes]

Or use COALESCE

Select COALESCE(Field1,'') as Field1, COALESCE(Field2,0) as Field2 FROM
myTable

From the BOL:

COALESCE
Returns the first nonnull expression among its arguments

COALESCE(expression1,...n) is equivalent to this CASE function:
CASE
WHEN (expression1 IS NOT NULL) THEN expression1
...
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL

 

[Matthew H. Toney]

Is you use IsDBNull(field) it should return the results correctly for you...