why do we use below statements in the stored procedures?

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Could any one please explain why do we use the 开发者_运维知识库above statements.

SET ANSI_NULLS

This setting makes it possible to do NULL comparisons using equality operator and not just IS [NOT] NULL operator.

-- when ON this is perfectly valid comparison
Col = NULL
-- when OFF only this is valid
Col IS NULL

SET QUOTED_IDENTIFIER

This example from MSDN/Books online will shed some light on this:

SET QUOTED_IDENTIFIER OFF
GO
-- An attempt to create a table with a reserved keyword as a name
-- should fail.
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);
GO

SET QUOTED_IDENTIFIER ON;
GO

-- Will succeed.
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);
GO

MSDN is your friend:

ANSI: http://msdn.microsoft.com/en-us/library/ms188048.aspx

QUOTED_IDENTIFIER: http://msdn.microsoft.com/en-us/library/ms174393.aspx

Description of ANSI NULLS

Description of QUOTED IDENTIFIER

When create or alter SQL object like Stored Procedure, User Defined Function in Query Analyzer, it is created with following SQL commands prefixed and suffixed.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO--SQL PROCEDURE, SQL FUNCTIONS, SQL OBJECTGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

ANSI NULL ON/OFF: This option specifies the setting for ANSI NULL comparisons. When this is on, any query that compares a value with a null returns a 0. When off, any query that compares a value with a null returns a null value.

QUOTED IDENTIFIER ON/OFF: This options specifies the setting for usage of double quotation. When this is on, double quotation mark is used as part of the SQL Server identifier (object name). This can be useful in situations in which identifiers are also SQL Server reserved words.