The ISNULL system function replaces NULL with the specified replacement value. The return data type is the same type as the first parameter. The syntax of the ISNULL system function is as follows:
ISNULL ( <check_expression>, <replacement_value> )
The <check_expression> parameter is the expression to be checked for NULL and can be of any type. The <replacement_value> parameter is the expression to be returned if <check_expression> is NULL. The <replacement_value> must be of a type that is implicitly convertible to the type of the <check_expression> parameter. The value of <check_expression> is returned if it is not NULL; otherwise, the value of the <replacement_value> is returned after it is implicitly converted to the type of the <check_expression> parameter, if the types are different.
Here are a few uses of the ISNULL system function:
Usage #1 : Generate a Comma-Delimited String
DECLARE @CSV VARCHAR(MAX)
SELECT @CSV = ISNULL(@CSV + ',', '') + [UserName]
FROM [dbo].[Users]
Usage #2 : Get Full Name from an Optional First Name and an Optional Last Name
SELECT ISNULL([LastName] + ', ', '') + ISNULL([FirstName], '') AS [FullName]
FROM [dbo].[Users]
Usage #3 : Listing NULL Values at the End of an Ordered Result Set
SELECT [FirstName], [LastName], [Salary]
FROM [dbo].[Employees]
ORDER BY ISNULL([Salary], 9999999) ASC
Usage #4 : Return the First Non-NULL Value from a List of Columns
SELECT ISNULL(ISNULL([PhoneNumber], [CellNumber]), [OfficeNumber]) AS [Contact Number]
FROM [dbo].[Customer]