isnull

Substitutes the value specified in expression2 when expression1 evaluates to NULL.

Syntax

isnull(expression1, expression2)

Parameters

expression – is a column name, variable, constant expression, or a combination of any of these that evaluates to a single value. It can be of any datatype, including unichar. expression is usually a column name. If expression is a character constant, it must be enclosed in quotes.

Examples

Example 1 – Returns all rows from the titles table, replacing null values in price with 0:
```
select isnull(price,0) 
from titles
```

Usage

isnull, a system function, substitutes the value specified in expression2 when expression1 evaluates to NULL. For general information about system functions, see Transact-SQL Users Guide.
The datatypes of the expressions must convert implicitly, or you must use the convert function.
If expression1 parameter is a char datatype and expression2 is a literal parameter, the results from your select statement that includes isnull differ based on whether you enable literal autoparameterization. To avoid this situation, do not autoparameterize char datatype literals within isnull().

Stored procedures that use isnull() with the same expression settings may also exhibit unexpected behavior. If this occurs, re-create the corresponding autoparameterizations.

See also Controlling Literal Parameterization in Performance and Tuning Series: Query Processing and Abstract Plans; System Administration Guide: Volume 1; Transact-SQL Users Guide

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute isnull.