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.

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

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:
-
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.
-
Remove the SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements from within the stored procedure.
-
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:
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;

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

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.


Design Rules, Code Smells

There is no additional info for this rule.

SQL
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) |

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 |
