Knowledge about database stored procedures

(i)

SET Ansi_nulls {on | OFF}

Specifies the SQL-92 compliance behavior of these operators when using the Equals (=) and not equal (<>) comparison operators on null values.

Notes

The SQL-92 standard requires a value of equal to (=) or Not equal to (<>) to be compared to FALSE for null values. When SET Ansi_nulls is on, a SELECT statement that uses WHERE column_name = NULL still returns 0 rows even if there is a null value in column_name . SELECT statements that use WHERE column_name <> NULL still return 0 rows, even if a non-null value exists in column_name .

When SET Ansi_nulls is OFF, the Equals (=) and not Equals (<>) comparison operators do not comply with the SQL-92 standard. Use the WHEREcolumn_name = null SELECT statement to return rows that contain null values in column_name . Use the WHERE column_name <> NULL SELECT statement to return rows that contain non-null values in the column. Also, use the SELECT statement in WHERE column_name <> Xyz_value to return all non-XYZ values and non-null rows.

Description Microsoft? SQL Server? Is whether to interpret the empty string as a single space or a true empty string, depending on the compatibility level setting of the sp_dbcmptlevel . If the compatibility level is less than or equal to 65,sql Server, the empty string is interpreted as a single space. If the compatibility level equals 70, SQL Server interprets the empty string as an empty string. For more information, see sp_dbcmptlevel.

When SET ansi_nulls is on, all comparisons of NULL values are evaluated to UNKNOWN. When SET ansi_nulls is OFF, if the data value is NULL, all data comparisons to null values are evaluated to TRUE. If not specified, the setting of the ANSI nulls option for the current database is applied. For more information about the ANSI nulls database option, see sp_dboption and setting Database options.

To make the script work as expected, the is null and is not NULL are used in comparisons that may contain null values, regardless of the ANSI nulls database option or SET ANSI_NULLS setting.

For stored procedures, SQL Server sets the value using the SET ANSI_NULLS when the stored procedure was originally created. Regardless of when the stored procedure is executed, the settings for SET ANSI_NULLS revert to the value they originally used and take effect. When you call set ANSI_NULLS within a stored procedure, its settings do not change.

Sets ANSI_NULLS should be set to on when executing distributed queries.

When you create or manipulate an index on a computed column or indexed view, the SET ansi_nulls must also be on. If SET ansi_nulls is OFF, the CREATE, UPDATE, INSERT, and DELETE statements on the indexed table on the computed column or indexed view will fail. SQL Server will return an error listing all SET options that violate the required values. Also, when the SELECT statement is executed, if SET ansi_nulls is OFF, SQL Server ignores the index values on the computed column or view and resolves the selection as if there were no such indexes on the table or view.

Description ANSI_NULLS is one of the seven set options that must be set to the desired value when working with indexes on computed columns or indexed views. The options ansi_padding, Ansi_warnings, ARITHABORT, QUOTED_IDENTIFIER, and Concat_null_yields_null must also be set to ON, and the Numeric_ Roundabort is set to OFF.

The SQL Server ODBC driver and the Microsoft OLE DB provider for SQL Server automatically set ANSI_NULLS to on when connected. This setting can be configured in ODBC data sources, ODBC connection attributes, or OLE DB connection properties that are set in the application before they are connected to SQL Server. For connections from Db-library applications, SET ansi_nulls defaults to OFF.

When set ANSI_DEFAULTS is on, set ANSI_NULLS is enabled.

Set ANSI_NULLS settings are set at execution or runtime, not at parse time.

Permissions

SET ansi_nulls permissions are granted to all users by default.

Example

The following example compares NULL and non-null values in a table using the Equals (=) and not equal (<>) comparison operators. The following example also shows that is NULL is not affected by the SET ANSI_NULLS setting.

--Create table t1 and insert values.
CREATE TABLE T1 (a int null)
INSERT into T1 values (NULL)
INSERT into T1 values (0)
INSERT into T1 values (1)
GO
--Print message and perform SELECT statements.
PRINT ' Testing default setting '
DECLARE @varname int
SELECT @varname = NULL
SELECT *
from T1
WHERE A = @varname
SELECT *
from T1
WHERE a <> @varname
SELECT *
from T1
WHERE A is NULL
GO
--SET ansi_nulls to ON and test.
PRINT ' Testing ansi_nulls on '
SET ANSI_NULLS on
GO
DECLARE @varname int
SELECT @varname = NULL
SELECT *
from T1
WHERE A = @varname
SELECT *
from T1
WHERE a <> @varname
SELECT *
from T1
WHERE A is NULL
GO
--SET Ansi_nulls to OFF and test.
PRINT ' testing SET ansi_nulls OFF '
SET Ansi_nulls OFF
GO
DECLARE @varname int
SELECT @varname = NULL
SELECT *
from T1
WHERE A = @varname
SELECT *
from T1
WHERE a <> @varname
SELECT *
from T1
WHERE A is NULL
GO
--Drop table T1.
DROP TABLE T1
GO

Please see

= (equals)

IF ... ELSE

<> (not equal to)

SET

SET Ansi_defaults

WHERE

While

Two

SET QUOTED_IDENTIFIER OFF

This is primarily a possible error for double quotation marks ( when a stored procedure is established ).

use off to make some database "" double quotes without error.

run out and remember on. Because the system is on by default.