sql92npw

Determines how NULL values are handled in concatenation and aggregate functions.

USYS$MSS_PARAMS sql92npw = on | off

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.

Description

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 ProcScript:

sql/print "SELECT ISNULL('type: ' + fruit, '(null)') AS fruit FROM null_fruit"

When sql92npw=off (or is not specified in MSS U5.1 or lower), this results in the following:

fruit
===============
type:
type: apple
type: banana

When sql92npw=on (or is not specified in MSS U5.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: