Export (0) Print
Expand All

Host Variables and Null Values

SQL Server 2000

  New Information - SQL Server 2000 SP3.

Unlike SQL, the C language does not support variables with null (unknown or missing) values. Embedded SQL enables you to store and retrieve null values from a database by using host indicator variables. Together, a host variable and its companion indicator variable specify a single SQL value. Each of the variables must be preceded by a colon (:). When a host variable is NULL, its indicator variable has the value -1. When a host variable is nonNULL, the value of the indicator variable specifies the maximum length of the host variable data.

Place indicator variables immediately after the corresponding host variable specified in the Embedded SQL statement. For example, the following embedded UPDATE statement uses a saleprice host variable with a companion saleprice_null indicator variable:

EXEC SQL UPDATE closeoutsale
   SET temp_price = :saleprice :saleprice_null, listprice = :oldprice;

In the following SELECT statement, price nullflag is set to -1 because the price of this book is NULL:

EXEC SQL 
SELECT price INTO :price:price nullflag
FROM titles
WHERE au_id = "mc3026"

Optionally, you can precede an indicator variable with the INDICATOR keyword when using a host variable and its associated indicator variable. For example, the following embedded UPDATE statement uses the INDICATOR keyword to more easily identify the indicator variable saleprice_null:

EXEC SQL UPDATE closeoutsale
   SET temp_price = :saleprice INDICATOR :saleprice_null;

If saleprice_null has a value of -1 when the UPDATE statement executes, Embedded SQL will change the statement to:

EXEC SQL UPDATE closeoutsale
   SET temp_price = null, listprice = :oldprice;

You cannot use indicator variables in a search condition. For example, you cannot use the following Embedded SQL statement:

EXEC SQL DELETE FROM closeoutsale
   WHERE temp_price = :saleprice :saleprice_null;

However, you can use the following technique to search for null values:

if (saleprice_null == -1)
{
   EXEC SQL DELETE FROM closeoutsale
      WHERE temp_price IS null;
}
else
{
   EXEC SQL DELETE FROM closeoutsale
      WHERE temp_price = :saleprice;
}

Security Note  Validate all user input. Do not concatenate user input before validating it. Never execute a command constructed from unvalidated user input. For more information, see Validating User Input.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft