SET ANSI_NULLS IN Stored Procedure:

Let’s create a sample table:

Create table #nulltest (
Id int null
, name varchar (30) null
)

Inserting sample rows:

Insert into #nulltest values (10,'ten');
Insert into #nulltest values (null, 'null value');
Insert into #nulltest values (null, 'null value2');
Insert into #nulltest values (5,'five');
Insert into #nulltest values (1,'one');

Select * from #nulltest;

id            name

10            ten

NULL        null value

NULL        null value2

5              five

1              one

(5 row(s) affected)

 When SET ANSI_NULLS is ON,

A SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. 

A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

Set ansi_nulls on

Select * from #nulltest where id=null

id          name                          

----------- ------------------------------

(0 row(s) affected)

 Select * from #nulltest where id<>null

id          name                          

----------- ------------------------------

(0 row(s) affected)

 

Select * from #nulltest where id<>10

id          name                          

----------- ------------------------------

5           five

1           one

(2 row(s) affected)

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. 

A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. 

A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column.

Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.

Set ansi_nulls off

Select * from #nulltest where id=null

id          name                          

----------- ------------------------------

NULL        null value

NULL        null value2

(2 row(s) affected)

 

Select * from #nulltest where id<>null

id          name                          

----------- ------------------------------

10          ten

5           five

1           one

(3 row(s) affected)

 Select * from #nulltest where id<>10

id          name                          

----------- ------------------------------

5           five

1           one

(2 row(s) affected)

When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN in the where clause UNKNOWN means faulse.  

When SET ANSI_NULLS is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL.

If SET ANSI_NULLS is not specified, the setting of the ANSI_NULLS option of the current database applies.

SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.

For a script to work as intended, regardless of the ANSI_NULLS database option or the setting of SET ANSI_NULLS, use IS NULL and IS NOT NULL in comparisons that might contain null values.

SET ANSI_NULLS must also be ON when you are creating or changing indexes on computed columns or indexed views. 

If SET ANSI_NULLS is OFF, any CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. SQL Server will return an error that lists all SET options that violate the required values.

Also, when you execute a SELECT statement, if SET ANSI_NULLS is OFF, SQL Server will ignore the index values on computed columns or views and resolve the select operation as if there were no such indexes on the tables or views.

When SET ANSI_DEFAULTS is ON, SET ANSI_NULLS is enabled.

The setting of SET ANSI_NULLS is set at execute or run time and not at parse time.

Permissions
Requires membership in the public role.

SET ANSI_NULLS IN Stored Procedure:

• The Database Engine saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a Transact-SQL stored procedure is created or altered.

• These original settings are used when the stored procedure is executed. Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are ignored during stored procedure execution.

Let’s create an sample SP:
Set ansi_nulls off
go
create procedure #proctest as
begin
select * from #nulltest where id=null;
end

The data base engine saves the settings(ansi_nulls off) of ANSI_NULLS for this SP, if you execute the SP with below command

Exec #proctest

Result set is:
id          name                          
----------- ------------------------------
NULL        null value
NULL        null value2
(2 row(s) affected)

Even if you execute with below script the result set is same as above.

Set ansi_nulls on
Go
Exec #proctest

Result set is:
id          name                          
----------- ------------------------------
NULL        null value
NULL        null value2
(2 row(s) affected)

·         SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements that occur within the stored procedure do not affect the functionality of the stored procedure.

Select * from #nulltest where id=null;

id          name                          

----------- ------------------------------

(0 row(s) affected)

From the above result set we can confirm ANSI_NULLS setting is ON

Create procedure #proctest as

Begin

Set ansi_nulls off

Select * from #nulltest where id=null;

End

Now execute the stored procedure:

Exec #proctest

Go

id          name                          

----------- ------------------------------

(0 row(s) affected)

• From the above result set we can conclude the ANSI_NULLS setting inside the stored   procedure has no effect.

Other SET options, such as SET ARITHABORT, SET ANSI_WARNINGS, or SET ANSI_PADDINGS are not saved when a stored procedure is created or altered.

If the logic of the stored procedure is dependent on a particular setting, include a SET statement at the start of the procedure to ensure the proper setting. When a SET statement is executed from a stored procedure, the setting remains in effect only until the stored procedure completes. The setting is then restored to the value it had when the stored procedure was called. This allows individual clients to set the options wanted without affecting the logic of the stored procedure.

Thanks...