Stored Procedure Set Options.. A potential gotcha

I have seen a lot of problems with stored procedures not working correctly, all of a sudden.  There can be numerous reasons why the stored procedure is not working correctly, but one of  the most overlooked culprits is SET options.  I wanted to talk about how SET options can affect query results and behavior. The behavior in question applies to two SET options and is fully documented in BOL, http://msdn.microsoft.com/en-us/library/ms187926.aspx. The two SET options are ANSI_NULLS and QUOTED_IDENTIFIER.  When a stored procedure is created, the set options that are enabled for that session, are stored in metadata.  For each subsequent run, the metadata settings are used and all explicit set options, outside of the stored procedure, are ignored. In SQL Server 2005 and greater, you can use the catalog view, sys.sql_modules, to view properties of a given object.  If you find yourself in a situation where you need to override the metadata, you should drop and recreate your stored procedure with the correct SET options. Here is a sample query to check the metadata of a given object.

SELECT *

FROM sys.sql_modules 

WHERE OBJECT_ID = OBJECT_ID('usp_TestProc')

Now that we know our objective, let’s get to the code.

We will start by creating a simple stored procedure that creates a table variable and selects some data.  We will turn the ANSI_NULLS OFF prior to the stored procedure creation. 

USE [tempdb]

GO

--drop the sp and recreate with the correct set options.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_TestProc]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[usp_TestProc]

GO

SET ANSI_NULLS OFF --<------------ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE usp_TestProc

AS

BEGIN

DECLARE @t TABLE(

id INT,

col CHAR(1)

)

INSERT INTO @t VALUES (1,'a');

INSERT INTO @t VALUES (2,NULL);

SELECT *

FROM @t 

WHERE col = NULL

END

GO

Now that we have our stored procedure. , let’s check the metadata in sys.sql_modules, by running the code below.

SELECT *

FROM sys.sql_modules 

WHERE OBJECT_ID = OBJECT_ID('usp_TestProc')

You should something like the screenshot below:

Now we are going to execute the stored procedure, making sure to explicitly make the SET options different than the stored procedure. Remember ANSI_NULLS are turned OFF, in the stored procedure; however, we are going to set it to ON.  We do not expect to see results because we are turning the option on and NULL should evaluate to unknown.

SET ANSI_NULLS ON

GO

EXEC [usp_TestProc]

GO

Results: The stored procedure returned results even though we turned on ANSI_NULLS.  This query should not have returned anything because using = or <> to a NULL should resolve as UNKNOWN;  however, the stored procedure metadata overrode our SET option and thus we get a result.

 

Lets switch this around by dropping the procedure and recreating it with ANSI_NULL on and then explicitly turn it off.

--drop the sp and recreate with the correct set options.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_TestProc]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[usp_TestProc]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE usp_TestProc

AS

BEGIN

DECLARE @t TABLE(

id INT,

col CHAR(1)

)

INSERT INTO @t VALUES (1,'a');

INSERT INTO @t VALUES (2,NULL);

SELECT *

FROM @t 

WHERE col = NULL

END

GO

Now let’s execute the stored procedure, with the new SET options.

SET ANSI_NULLS OFF;

GO

EXEC [usp_TestProc]

GO

Results:  There were no results because ANSI_NULLS is turned on, which means you cannot use = or <> when comparing to NULL, regardless that we turned it OFF explicitly.

As you can see, even though we explicitly set ANSI_NULLS the option is ignored because the stored procedure’s metadata overrides all. This can make debugging a stored procedure a little more difficult, but it is a very good fact to know.  You can use a set option with in the stored procedure itself, which should override any global settings, for the duration of the stored procedure.  I have seen this problem bite people on the $%^ a few too many times and hopefully by reading this you will become more aware of the potential pitfalls, regarding SET options.