SQL Server Documentation

Home > MSDN Documentation > SQL > Functions > Scalar > Logical

Logical Scalar Functions

Logical scalar functions evaluate conditions and return a Boolean result (TRUE, FALSE, or UNKNOWN). These functions are crucial for controlling query logic, data validation, and conditional processing within SQL Server.

IIF

IIF ( boolean_expression, true_value, false_value )

Evaluates a boolean expression and returns one of two values based on the result of the expression. This is a shorthand for a CASE expression.

Parameters:
  • boolean_expression: An expression that evaluates to TRUE, FALSE, or UNKNOWN.
  • true_value: The value to return if boolean_expression evaluates to TRUE.
  • false_value: The value to return if boolean_expression evaluates to FALSE or UNKNOWN.
Returns:

Returns the data type of the value with the higher precedence from true_value and false_value.

Example:
SELECT
    ProductName,
    IIF(UnitPrice > 50, 'Expensive', 'Affordable') AS PriceCategory
FROM
    Products;

ISDATE

ISDATE ( expression )

Determines if the input expression is a valid date or datetime value. Returns 1 (TRUE) if it is valid, and 0 (FALSE) otherwise.

Parameters:
  • expression: The expression to test.
Returns:

BIT (1 for TRUE, 0 for FALSE).

Example:
SELECT ISDATE('2023-10-27'); -- Returns 1
SELECT ISDATE('Not a date'); -- Returns 0

ISNULL

ISNULL ( check_expression , replacement_value )

Replaces NULL values with a specified replacement value. If check_expression is not NULL, check_expression is returned. If check_expression is NULL, replacement_value is returned.

Parameters:
  • check_expression: The expression to check for NULL.
  • replacement_value: The value to return if check_expression is NULL.
Returns:

Returns the data type of the expression with the higher precedence between check_expression and replacement_value.

Example:
SELECT
    ProductName,
    ISNULL(Color, 'N/A') AS ProductColor
FROM
    Products;

ISNUMERIC

ISNUMERIC ( expression )

Indicates whether an expression is a valid numeric type. Returns 1 (TRUE) if it is, 0 (FALSE) otherwise. Note: This function can return 1 for values that are not strictly numeric types, such as currency symbols or signs.

Parameters:
  • expression: The expression to test.
Returns:

BIT (1 for TRUE, 0 for FALSE).

Example:
SELECT ISNUMERIC('123.45'); -- Returns 1
SELECT ISNUMERIC('$100'); -- Returns 1 (may be unexpected)
SELECT ISNUMERIC('abc'); -- Returns 0

NULLIF

NULLIF ( expression1 , expression2 )

Returns NULL if expression1 is equal to expression2; otherwise, returns expression1. This is useful for detecting and handling specific value matches that should be treated as null.

Parameters:
  • expression1: The first expression.
  • expression2: The second expression.
Returns:

Returns the data type of the expression with the higher precedence between expression1 and expression2.

Example:
SELECT
    ProductID,
    NULLIF(Quantity, 0) AS NonZeroQuantity
FROM
    OrderDetails;
-- If Quantity is 0, NonZeroQuantity will be NULL. Otherwise, it will be the Quantity value.

Related Topics