SQLSetParamNull() function

The SQLSetParamNull() function sets a specified parameter within a SQL statement to NULL.

Category

SQL

Syntax

SQLSetParamNull(StatementID, ParameterNumber, ParameterType, Precision, Scale)

Arguments

StatementID

Integer value that identifies a SQL statement within a query.

ParameterNumber

Integer value that identifies the parameter in the SQL statement identified by the StatementID argument.

ParameterType

Integer value that specifies the type of data associated with the parameter specified by the ParameterNumber argument. The ParameterType argument can be assigned the following values:

0: String

1: Date/time

2: Integer

3: Floating point number

4: Decimal number

Precision

Precision of the data associated with the parameter data type.

Scale

Decimal value's scale. This value is required only if applicable to the parameter being set to null.

Remarks

Comparison with the NULL value is controlled by the ANSI_NULLS option in SQL Server. In SQL Server 7.0, this option is resolved at object creation time, not at query execution time. When a stored procedure is created in SQL Server 7.0, this option is ON by default and thus a clause such as "WHERE MyField = NULL" always returns NULL (FALSE) and no row is returned from a SELECT statement using this clause.

In order for the comparison = or <> to return TRUE or FALSE, it is necessary to set the option to OFF when creating the stored procedure. If the ANSI_NULLS is not set to OFF, then SQLSetParamNull() does not work as expected. In this case, comparison against NULL value should use the syntax "WHERE MyField IS NULL" or "WHERE MyField IS NOT NULL".

Example

This transaction set returns all rows of the Products table where the ProductName is not NULL.

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE sp_TestNotNull @ProductParam varchar(255)

AS SELECT * FROM Products WHERE ProductName <> @ProductParam

GO

SET ANSI_NULLS ON

GO

InTouch can run the following SQL Access scripts.

ResultCode = SQLSetStatement(ConnectionId, "sp_TestNotNull");

ResultCode = SQLPrepareStatement(ConnectionId, StatementID);

ResultCode = SQLSetParamNull(StatementID, 1, 0, 0, 0);

ResultCode = SQLExecute(ConnectionId, BindList, StatementID);

ResultCode = SQLFirst(ConnectionId);

ResultCode = SQLClearStatement(ConnectionId, StatementID);

See Also

SQLPrepareStatement()