Microsoft SQL Server stored procedures are traditionally described as a group of T-SQL statements compiled into a single execution plan to allow for a consistent implementation of business logic across the application and also improve performance due to plan caching.

The SET options defined for a connection influence query execution and query results. Because SQL Server needs to cache the stored procedure execution plan and reuse it across multiple connections, stored procedures exhibit slightly different behavior around SET options when compared to ad-hoc queries. Often, these differences in behavior are overlooked when developing deployment scripts and this mistake can lead to undesired behavior in the application.

In this article, we will look at this special behavior with examples.

ANSI_WARNINGS - SET option ignored by Stored Procedures

ANSI_WARNINGS control the ISO behavior around various error conditions. For example the behavior of a query in the following situations is influenced by ANSI_WARNINGS:

• Encountering a NULL value during an aggregation operation
• Encountering a Divide by Zero error
• String truncation

When passing values for stored procedure & user-defined function parameters, the SET ANSI_WARNINGS option is ignored.

Let’s see this with the help of an example. The query shown below demonstrates the expected behavior of ANSI_WARNINGS when used in an ad-hoc query. The query attempts to insert some test data into a table twice:

• The first attempt is made with ANSI_WARNINGS set to ON, because of which we receive a string truncation error (Msg 8152) when the test data exceeds the maximum limit for the column
• The second attempt is made with ANSI_WARNINGS set to OFF, because of which we see that the input string in the test data is truncated silently

DECLARE @userOptions INT;
SELECT @userOptions = @@OPTIONS;
--Let us first confirm that ANSI_WARNINGS is ON
IF ((@userOptions & 8) = 8)
    PRINT 'ANSI_WARNINGS is ON'
--In the first batch, ANSI_WARNINGS is ON and hence,
--we should receive a string truncation error [Msg 8152]
BEGIN
    DECLARE @testTable TABLE (FieldValue CHAR(3));
    --Conduct the test insert
    INSERT INTO @testTable (FieldValue)
    VALUES ('Microsoft');
    --Select values from the table
    SELECT tt.FieldValue
    FROM @testTable AS tt;
END
GO
DECLARE @userOptions INT;
SELECT @userOptions = @@OPTIONS;
--Now, let us turn ANSI_WARNINGS OFF
SET ANSI_WARNINGS OFF;
--Let us first confirm that ANSI_WARNINGS is indeed OFF
IF ((@userOptions & 8) != 8)
    PRINT 'ANSI_WARNINGS is OFF'
--In this batch, ANSI_WARNINGS is OFF and hence,
--we would not receive any errors;
BEGIN
    DECLARE @testTable TABLE (FieldValue CHAR(3));
    --Conduct the test insert
    INSERT INTO @testTable (FieldValue)
    VALUES ('Microsoft');
    --Select values from the table
    SELECT tt.FieldValue
    FROM @testTable AS tt;
END
GO

The ressults of the first insert:

and the second:

Now, let us convert this example into a stored procedure, while ensuring that the ANSI_WARNINGS is set to ON.

--Now, create a stored procedure for the same test
--But first, let's ensure that ANSI_WARNINGS are set to ON
DECLARE @userOptions INT;
SELECT @userOptions = @@OPTIONS;
--Let us first confirm that ANSI_WARNINGS is ON
IF ((@userOptions & 8) = 8)
    PRINT 'ANSI_WARNINGS is ON'
GO
IF OBJECT_ID('dbo.proc_ANSIWarningTest','P') IS NOT NULL
    DROP PROCEDURE dbo.proc_ANSIWarningTest;
GO
CREATE PROCEDURE dbo.proc_ANSIWarningTest
    @inputValue CHAR(3)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @testTable TABLE (FieldValue CHAR(3));
    INSERT INTO @testTable (FieldValue)
    SELECT @inputValue;
    SELECT tt.FieldValue
    FROM @testTable AS tt;
END
GO

Now, we will execute the stored procedure with ANSI_WARNINGS set to ON and OFF, while passing a parameter value which we know should generate a string truncation error. In both cases, we see that silent truncation happens and the ANSI_WARNINGS setting is ignored.

--With ANSI_WARNINGS set to ON, passing a value greater than the parameter size (CHAR(3))
--will NOT cause the error.
--This is because ANSI_WARNINGS is ignored when passing parameters to a procedure
DECLARE @userOptions INT;
SELECT @userOptions = @@OPTIONS;
--Let us first confirm that ANSI_WARNINGS is ON
IF ((@userOptions & 8) = 8)
    PRINT 'ANSI_WARNINGS is ON'
EXEC dbo.proc_ANSIWarningTest @inputValue = 'Microsoft';
GO

The results:

Lesson Learnt: Do not rely on ANSI_WARNINGS to detect string truncation when sending parameters to stored procedures and functions. These checks need to be performed by the calling application before executing the procedure/user defined function.

ANSI_NULLS and QUOTED_IDENTIFIER – SET option stored by Stored Procedures

The ANSI_NULLS option controls how the SQL Server compares a NULL value. When set to ON, a NULL value cannot be compared using the equal to (=) operator.

Here is a quick example of how the ANSI_NULLS setting behaves for an ad-hoc query:

--Declare a test table, and insert some test data
DECLARE @AnsiNullsTesting TABLE (RecordId INT, RecordValue VARCHAR(20));
INSERT INTO @AnsiNullsTesting (RecordId, RecordValue)
VALUES (1, 'Microsoft'),
       (NULL, 'SQL Server');
--With ANSI_NULLS ON, attempt to compare a NULL with the equal to (=) operator
SET ANSI_NULLS ON;
SELECT * FROM @AnsiNullsTesting WHERE RecordId = NULL;
--With ANSI_NULLS OFF, attempt to compare a NULL with the equal to (=) operator
SET ANSI_NULLS OFF;
SELECT * FROM @AnsiNullsTesting WHERE RecordId = NULL;

The results:

The QUOTED_IDENTIFIER setting allows us to identify literals, i.e. SQL Server objects with double quotes. This setting allows us to specify SQL Server reserved keywords and/or characters not allowed by T-SQL as object names when enclosed within double quotation marks.

SET QUOTED_IDENTIFIER ON;
--With QUOTED_IDENTIFIER set to ON,
--attempt to create a table using a researved keyword will succeed
CREATE TABLE "Select" (SelectionId INT,
                       SelectedValue VARCHAR(10));
SELECT * FROM dbo."Select";
--Cleanup
DROP TABLE dbo."Select";
GO
--With QUOTED_IDENTIFIER set to OFF,
--attempt to create a table using a researved keyword will fail
SET QUOTED_IDENTIFIER OFF;
CREATE TABLE "Select" (SelectionId INT,
                       SelectedValue VARCHAR(10));
SELECT * FROM dbo."Select";
--Cleanup
DROP TABLE dbo."Select";
GO

We get these results from the script above:

Now let us create a stored procedure which simply inserts two records into a table with the same name as a SQL Server reserved keyword (i.e. “Select”) and subsequently attempts to find which of the two records has a NULL value using an equal to (=) comparison. We will create this stored procedure with ANSI_NULLS set to OFF and QUOTED_IDENTIFIER set to ON.

--With ANSI_NULLS set to OFF, and QUOTED_IDENTIFIER set to ON,
--let us create a stored procedure
SET ANSI_NULLS OFF;
SET QUOTED_IDENTIFIER ON;
GO
IF OBJECT_ID('dbo.proc_AnsiNullsAndQuotedIdentifierTesting','P') IS NOT NULL
    DROP PROCEDURE dbo.proc_AnsiNullsAndQuotedIdentifierTesting;
GO
CREATE PROCEDURE dbo.proc_AnsiNullsAndQuotedIdentifierTesting
AS
BEGIN
    --With QUOTED_IDENTIFIER set to ON,
    --attempt to create a table using a researved keyword will succeed
    IF OBJECT_ID('dbo.Select') IS NOT NULL
        DROP TABLE dbo."Select";
    CREATE TABLE "Select" (SelectionId INT,
                           SelectedValue VARCHAR(10));
    INSERT INTO "Select" (SelectionId, SelectedValue)
    VALUES (1, 'Microsoft'),
           (NULL, 'SQL Server');
    SELECT * FROM dbo."Select" WHERE SelectionId = NULL;
END;
GO

We will now execute this stored procedure in various different combinations of ANSI_NULLS and QUOTED_IDENTIFIER. In each combination, the stored procedure execution will be successful and will return the same result.

--Now, turn ANSI_NULLS and QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF;
SET QUOTED_IDENTIFIER OFF;
GO
EXEC dbo.proc_AnsiNullsAndQuotedIdentifierTesting;
GO
--Now, turn ANSI_NULLS and QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
EXEC dbo.proc_AnsiNullsAndQuotedIdentifierTesting;
GO

The results:

We get these results because when the stored procedure was created, Microsoft SQL Server stored the configuration of the ANSI_NULLS and QUOTED_IDENTIFIER settings.

At this stage, we can also spend some time to confirm whether changing these options inside the stored procedure would have made any difference or not. To do this, we will take the same procedure as we did in the steps above, but now invert the ANSI_NULLS and QUOTED_IDENTIFIER settings inside the stored procedure.

--With ANSI_NULLS set to OFF, and QUOTED_IDENTIFIER set to ON,
--let us create a stored procedure
SET ANSI_NULLS OFF;
SET QUOTED_IDENTIFIER ON;
GO
IF OBJECT_ID('dbo.proc_AnsiNullsAndQuotedIdentifierTesting','P') IS NOT NULL
    DROP PROCEDURE dbo.proc_AnsiNullsAndQuotedIdentifierTesting;
GO
CREATE PROCEDURE dbo.proc_AnsiNullsAndQuotedIdentifierTesting
AS
BEGIN
    SET QUOTED_IDENTIFIER OFF;
    SET ANSI_NULLS ON;
    --With QUOTED_IDENTIFIER set to ON,
    --attempt to create a table using a researved keyword will succeed
    IF OBJECT_ID('dbo.Select') IS NOT NULL
        DROP TABLE dbo."Select";
    CREATE TABLE "Select" (SelectionId INT,
                           SelectedValue VARCHAR(10));
    INSERT INTO "Select" (SelectionId, SelectedValue)
    VALUES (1, 'Microsoft'),
           (NULL, 'SQL Server');
    SELECT * FROM dbo."Select" WHERE SelectionId = NULL;
END;
GO

Upon repeating the stored procedure execution, we see that the ANSI_NULLS and QUOTED_IDENTIFIER settings defined inside the stored procedure have no effect.

--Now, turn ANSI_NULLS and QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF;
SET QUOTED_IDENTIFIER OFF;
GO
EXEC dbo.proc_AnsiNullsAndQuotedIdentifierTesting;
GO
--Now, turn ANSI_NULLS and QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
EXEC dbo.proc_AnsiNullsAndQuotedIdentifierTesting;
GO

Here are the results from the two executions above:

Lessons Learnt:

• When designing and documenting a stored procedure, it is important to decide and document the setting of ANSI_NULLS and QUOTED_IDENTIFIER that the procedure will use
• When generating deployment scripts for stored procedures and functions, ensure that the scripts for appropriate ANSI_NULLS and QUOTED_IDENTIFIER options are also included
• ANSI_NULLS and QUOTED_IDENTIFIER settings defined inside a stored procedure definition have no effect on the stored procedure

Other SET options – dependent upon the client connection

The other set options like ARITHABORT, ANSI_NULL_DFLT_ON and others are not “stored” with the stored procedure. The execution of the procedure execution uses these options from the client connection.  What this indicates is that these settings need to be specified inside the body of the stored procedure if the stored procedure logic is dependent upon a particular setting.

To demonstrate this with an example, we will take a stored procedure that creates a temporary table inside in the procedure body, inserts some test data and finally selects records from the temporary table.

IF OBJECT_ID('dbo.proc_AnsiNullDefaultOnTesting','P') IS NOT NULL
    DROP PROCEDURE dbo.proc_AnsiNullDefaultOnTesting;
GO
CREATE PROCEDURE dbo.proc_AnsiNullDefaultOnTesting
AS
BEGIN
    IF OBJECT_ID('tempdb..#Product') IS NOT NULL
        DROP TABLE #Product;
    CREATE TABLE #Product (ProductId INT IDENTITY(1,1),
                           ProductName VARCHAR(10),
                           Manufacturer VARCHAR(10)
                          );
    INSERT INTO #Product (ProductName, Manufacturer)
    VALUES ('SQL Server','Microsoft');
    --This statement will tell us what are the default values
    --When undefined, the database engine will typically try to insert NULLS
    INSERT INTO #Product DEFAULT VALUES;
    SELECT * FROM #Product;
    IF OBJECT_ID('tempdb..#Product') IS NOT NULL
        DROP TABLE #Product;
END;
GO

Now, we will execute the stored procedure with and without the ANSI_NULL_DFLT_ON option set. We notice that when ANSI_NULL_DFLT_ON is OFF, the insert into the temporary table with default values will fail because the database engine will not allow a value of NULL in a column which has not explicitly been defined to allow NULL values.

--Now, turn ANSI_NULLS and QUOTED_IDENTIFIER OFF
SET ANSI_NULL_DFLT_ON OFF;
EXEC dbo.proc_AnsiNullDefaultOnTesting;
GO
--Now, turn ANSI_NULLS and QUOTED_IDENTIFIER ON
SET ANSI_NULL_DFLT_ON ON;
EXEC dbo.proc_AnsiNullDefaultOnTesting;
GO

We receive these results:

Here is the messages pane:

As a solution, one can either define the required set option inside the body of the stored procedure and/or explicitly declare the NULL-ability on the temporary table. The recommended stored procedure definition is provided below.

IF OBJECT_ID('dbo.proc_AnsiNullDefaultOnTesting','P') IS NOT NULL
    DROP PROCEDURE dbo.proc_AnsiNullDefaultOnTesting;
GO
CREATE PROCEDURE dbo.proc_AnsiNullDefaultOnTesting
AS
BEGIN
    --Explicitly define all requried SET options here
    SET ANSI_NULL_DFLT_ON ON;
    IF OBJECT_ID('tempdb..#Product') IS NOT NULL
        DROP TABLE #Product;
    --Explicitly declare the NULL-ability of the columns on the temporay table
    CREATE TABLE #Product (ProductId INT IDENTITY(1,1),
                           ProductName VARCHAR(10) NULL,
                           Manufacturer VARCHAR(10) NULL
                          );
    INSERT INTO #Product (ProductName, Manufacturer)
    VALUES ('SQL Server','Microsoft');
    --This statement will tell us what are the default values
    --When undefined, the database engine will typically try to insert NULLS
    INSERT INTO #Product DEFAULT VALUES;
    SELECT * FROM #Product;
    IF OBJECT_ID('tempdb..#Product') IS NOT NULL
        DROP TABLE #Product;
END;
GO

Lessons Learnt:

• Always explicitly override all connection specific SET options inside the stored procedure body to avoid unexpected results/behavior
• Always explicitly specify the NULL-ability of all columns on temporary tables created within a stored procedure

Disclaimer

A few disclaimers about this information and code:

• The scripts in this article are provided "as-is", i.e. without warranties of any kind and are intended for demonstration purposes only.
• Request you to use these scripts for understanding and study purposes in your development environments only - they are not meant for use in production. The author is not responsible for any damage caused by misuse of these scripts.
• Scripts are tested on SQL Server 2008 R2.
• The opinions expressed herein are his own personal opinions and do not represent his employer’s view in any way.

About the author

Nakul Vachhrajani is a Technical Specialist and systems development professional with iGATE having a total IT experience of more than 8 years. Nakul is an active blogger with BeyondRelational.com (285+ blogs), and can also be found on forums at SQLServerCentral and BeyondRelational.com. Nakul has also been a guest columnist for SQLAuthority.com and SQLServerCentral.com. Nakul presented a webcast on the “Underappreciated Features of Microsoft SQL Server” at the Microsoft Virtual Tech Days Exclusive Webcast series (May 02-06, 2011) on May 06, 2011. He is also the author of a research paper on Database upgrade methodologies, which was published in a Computer Society of India (CSI) journal, published nationwide. In addition to his passion about SQL Server, Nakul also contributes to the academia out of personal interest. He visits various colleges and universities as an external faculty to judge project activities being carried out by the students.

Blog | LinkedIn | Twitter | Google+