Determines how NULL values are handled in concatenation and aggregate functions.
USYS$MSS_PARAMS sql92npw = on |
on—NULL values are treated as NULL values. The connector follows ANSI standards for handling NULL values, character data type padding, and warnings. This is the default for MSS connector version 5.2 and higher.
off—NULL values are treated as empty strings. This is the default for connector versions 5.1 and lower.
Note: Microsoft is deprecating the non-ANSI standard behavior in future versions of MS SQL Server.
Differences in Behavior
Note: If you do not have this option set in your assignment file, this may affect your application when you use the sql Proc command to explicitly concatenate columns in which one or more values are null.
For example, consider the following table called null_fruit.
fruit ========== (null) apple banana
For which you execute the following SQL in Proc:
sql/print "SELECT ISNULL('type: ' + fruit, '(null)') AS fruit FROM null_fruit"
=off (or is not specified in MSS 5.1 or lower), this results in the following:
fruit =============== type: type: apple type: banana
=on (or is not specified in MSS 5.2 or higher), this results in the following:
fruit =============== (null) type: apple type: banana
The concatenation of '
type: ' and a null value has now resulted in
null because null values are considered unknown values and not empty strings.
Should you want to explicitly consider null values as empty strings, you can use the ISNULL or COALESCE functions in the sql statement. For example:
sql/print "SELECT 'type:' + ISNULL(fruit, '') AS fruit FROM null_fruit" or sql/print "SELECT 'type:' + COALESCE(fruit, '') AS fruit FROM null_fruit"
This will result in the same behavior in both versions of the connector.
For more information, refer to the Microsoft documentation: