How to Deal with SET ANSI_NULLS ON or OFF ?

Question

I want to call this procedure that sends one value that can be NULL or any int value.

SELECT DomainName, DomainCode FROM Tags.tblDomain WHERE SubDomainId =@SubDomainId

I simply want to use this single query rather than what i m doing right now in below given code.

I searched for this how could i do this then i got this Link.

According to this I have to set ANSI_NULLS OFF

I am not able to set this inside this procedure before executing my sql query and then reset it again after doing this.

ALTER PROCEDURE [Tags].[spOnlineTest_SubDomainSelect] 
    @SubDomainId INT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Insert statements for procedure here
    IF @SubDomainId IS NULL
        SELECT DomainName, DomainCode FROM Tags.tblDomain WHERE SubDomainId IS NULL 
    ELSE
        SELECT DomainName, DomainCode FROM Tags.tblDomain WHERE SubDomainId =@SubDomainId
END

What will be the better practice to do deal with ANSI_NULLS or Using If Else

Answer 1

Can't you use a single query?

SELECT DomainName, DomainCode
FROM Tags.tblDomain
WHERE ( @SubDomainId IS NULL AND SubDomainId IS NULL )
   OR ( SubDomainId = @SubDomainId )

Answer 2

FYI, I'm pretty sure ...

ANSI_NULLS OFF

Applies to the procedure when you create/edit it, it's like a setting of the procedure.

So either the procedure has it ON or OFF. Your example was a query not a procedure so I'm a little confused.

But if you have SQL 2005/2008 for example if you "edit" procedure it opens up your procedure in a new tab you'll see the ANSI_NULLS OFF near the top.

You can edit it there and set it ON or OFF and update it to change ...

Answer 3

SET ANSI_NULLS is ony defined at stored proc create time and cannot be set at run time.

From CREATE PROC

Using SET Options

The Database Engine saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a Transact-SQL stored procedure is created or modified. 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 when the stored procedure is running. Other SET options, such as SET ARITHABORT, SET ANSI_WARNINGS, or SET ANSI_PADDINGS are not saved when a stored procedure is created or modified. If the logic of the stored procedure depends on a particular setting, include a SET statement at the start of the procedure to guarantee the appropriate setting. When a SET statement is executed from a stored procedure, the setting remains in effect only until the stored procedure has finished running. The setting is then restored to the value the stored procedure had when it was called. This enables individual clients to set the options they want without affecting the logic of the stored procedure.

The same appllies to SET QUOTED_IDENTIFIER

In this case, use IF ESLE because SET ANSI_NULLS will be ON in the future.

Or Peter Lang's suggestion.

To be honest, expecting SubDomainId = @SubDomainId to work when @SubDomainId is NULL is not really correct usage of NULL...

Answer 4

To better understand ANSI_NULLS check it here. http://praveenbattula.blogspot.com/2009/05/use-of-set-ansinulls-on-in-sql-server.html