Substitutes the value specified in expression2 when expression1 evaluates to NULL.
isnull(expression1, expression2)
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.
Returns all rows from the titles table, replacing null values in price with 0:
select isnull(price,0) from titles
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.
ANSI SQL – Compliance level: Transact-SQL extension.
Any user can execute isnull.
Documentation “Controlling literal parameterization” in Performance and Tuning Series: Query Processing and Abstract Plans, System Administration Guide: Volume 1, Transact-SQL Users Guide
Function convert
