SA0091 : Setting the QUOTED_IDENTIFIERS or ANSI_NULLS options inside stored procedure, trigger or function will have no effect

Introduction

Setting the QUOTED_IDENTIFIERS or ANSI_NULLS option inside the body of stored procedures is ignored and will not be effective.

Description

When T-SQL developers configure QUOTED_IDENTIFIERS or ANSI_NULLS within the body of a stored procedure in SQL Server, it leads to ineffective settings.

For example:

-- Example of ineffective settings
CREATE PROCEDURE ExampleProcedure
AS
BEGIN
    SET QUOTED_IDENTIFIER ON;
    SET ANSI_NULLS ON;
    SELECT * FROM TableName;
END;

Settings like QUOTED_IDENTIFIERS or ANSI_NULLS are ignored inside stored procedures, triggers, or functions, causing potential logical errors or incorrect results.

These settings affect how SQL Server interprets and executes certain T-SQL syntax, including how identifiers are quoted and how NULL values are compared.
Ignoring these options inside stored procedures can inadvertently cause different behavior than expected, leading to bugs and maintenance issues.

How to fix

This guidance addresses issues related to defining QUOTED_IDENTIFIERS or ANSI_NULLS settings within stored procedures, which can lead to ineffective settings and potential logical errors.

Follow these steps to address the issue:

1.Examine the stored procedure to locate any SET QUOTED_IDENTIFIER or SET ANSI_NULLS statements. These settings should not be placed inside the procedure body.

2.Remove the SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements from within the stored procedure.

3.Set SET QUOTED_IDENTIFIER and SET ANSI_NULLS outside the stored procedure creation statement if needed, ensuring they are configured at the session or batch level before executing the procedure.

For example:

-- Prior to creating the procedure, set the necessary options at the session level:
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;

-- Create the stored procedure without these settings inside
CREATE PROCEDURE ProperExampleProcedure
AS
BEGIN
    SELECT * FROM TableName;
END;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

20 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

CREATE PROCEDURE test_proc AS

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF
SET ARITHABORT OFF
SET ANSI_NULL_DFLT_ON OFF
SET ANSI_DEFAULTS OFF
SET ANSI_WARNINGS OFF
SET ANSI_PADDING OFF
SET CONCAT_NULL_YIELDS_NULL OFF
SET NUMERIC_ROUNDABORT ON
SET NOCOUNT ON
SET ROWCOUNT 100
SET XACT_ABORT ON
SET IMPLICIT_TRANSACTIONS ON
SET ARITHIGNORE OFF
SET LOCK_TIMEOUT 1
SET FMTONLY ON
SET NOEXEC ON
SET PARSEONLY OFF

SELECT au_lname, au_fname, au_id from authors
WHERE au_lname like 'L%'
--Option (Keep Plan)

Analysis Results

	Message	Line	Column
1	SA0091 : Setting the QUOTED_IDENTIFIER option will have no effect when done inside stored procedure, trigger or function.	3	4
2	SA0091 : Setting the ANSI_NULLS option will have no effect when done inside stored procedure, trigger or function.	4	4

Analysis Rules