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

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:

SQL
Copy
1-- Example of ineffective settings
2CREATE PROCEDURE ExampleProcedure
3AS
4BEGIN
5    SET QUOTED_IDENTIFIER ON;
6    SET ANSI_NULLS ON;
7    SELECT * FROM TableName;
8END;

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:

SQL
Copy
 1-- Prior to creating the procedure, set the necessary options at the session level:
 2SET QUOTED_IDENTIFIER ON;
 3SET ANSI_NULLS ON;
 4
 5-- Create the stored procedure without these settings inside
 6CREATE PROCEDURE ProperExampleProcedure
 7AS
 8BEGIN
 9    SELECT * FROM TableName;
10END;

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.
Categories

Design Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1CREATE PROCEDURE test_proc AS
 2
 3SET QUOTED_IDENTIFIER OFF 
 4SET ANSI_NULLS OFF 
 5SET ARITHABORT OFF 
 6SET ANSI_NULL_DFLT_ON OFF
 7SET ANSI_DEFAULTS OFF
 8SET ANSI_WARNINGS OFF 
 9SET ANSI_PADDING OFF
10SET CONCAT_NULL_YIELDS_NULL OFF
11SET NUMERIC_ROUNDABORT ON 
12SET NOCOUNT ON 
13SET ROWCOUNT 100 
14SET XACT_ABORT ON 
15SET IMPLICIT_TRANSACTIONS ON
16SET ARITHIGNORE OFF 
17SET LOCK_TIMEOUT 1
18SET FMTONLY ON 
19SET NOEXEC ON
20SET PARSEONLY OFF
21
22SELECT au_lname, au_fname, au_id from authors
23WHERE au_lname like 'L%'
24--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
See Also

Other Resources

Analysis Rules