Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to Deal with SET ANSI_NULLS ON or OFF?

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

like image 362
Shantanu Gupta Avatar asked Mar 20 '10 10:03

Shantanu Gupta


People also ask

Should ANSI_NULLS be on or off?

ANSI_NULLS should be set to ON for executing distributed queries. 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.

What is the use of set ANSI_NULLS ON GO SET Quoted_identifier on go?

When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings are captured and used for subsequent invocations of that stored procedure. When executed inside a stored procedure, the setting of SET QUOTED_IDENTIFIER is not changed. When SET ANSI_DEFAULTS is ON, QUOTED_IDENTIFIER is also ON.

Why we use set Nocount on in stored procedure?

SET NOCOUNT ON prevents the sending of DONEINPROC messages to the client for each statement in a stored procedure.

What is set Ansi_padding off?

The default for SET ANSI_PADDING is OFF for connections from DB-Library applications. The SET ANSI_PADDING setting does not affect the nchar, nvarchar, ntext, text, image, varbinary(max), varchar(max), and nvarchar(max) data types. They always display the SET ANSI_PADDING ON behavior.


2 Answers

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 applies to SET QUOTED_IDENTIFIER

In this case, use IF ELSE 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...

like image 92
gbn Avatar answered Oct 18 '22 01:10

gbn


Can't you use a single query?

SELECT DomainName, DomainCode
FROM Tags.tblDomain
WHERE ( @SubDomainId IS NULL AND SubDomainId IS NULL )
   OR ( SubDomainId = @SubDomainId )
like image 29
Peter Lang Avatar answered Oct 18 '22 03:10

Peter Lang