Use of SET ANSI_NULLS ON in SQL SERVER stored procedures

As we discussed the use of NOCOUNT in previous post , we also use another statement with all the stored procedures in sql server. That is SET ANSI_NULLS ON/OFF

Which is useful when,
Specifies SQL-92 compliant behavior of the Equals (=) and Not Equal to (<>) comparison operators when used with null values.

Syntax: SET ANSI_NULLS {ON | OFF}
The SQL-92 standard requires that an equals (=) or not equal to (<>) comparison against a null value evaluates to FALSE.

When SET ANSI_NULLS is ON, a SELECT statement using WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement using WHERE column_name <> NULL returns zero rows even if there are non-null values in column_name.

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the SQL-92 standard. A SELECT statement using WHERE column_name = NULL returns the rows with null values in column_name. A SELECT statement using WHERE column_name <> NULL returns the rows with non-null values in the column.