DF004: The SET statement is used within a stored procedure.

Last modified: December 25, 2024

The topic describes the DF004 T-SQL code analysis rule.

Category

PERFORMANCE

Message

The SET statement is used within a stored procedure.

Description

Changing the SET options for ANSI_DEFAULTS, ANSI_NULL_DFLT_OFF, ANSI_NULL_DFLT_ON, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, DATEFIRST, DATEFORMAT, LANGUAGE, NO_BROWSETABLE, or NUMERIC_ROUNDABORT leads to the recompilation of the affected stored procedure.

Additional information

During execution or runtime, all SET statements operate, with the exception of those running at parse time, including SET FIPS_FLAGGER, SET OFFSETS, SET PARSEONLY, and SET QUOTED_IDENTIFIER.

Noncompliant code example

CREATE PROCEDURE dbo.SelectUserData
AS BEGIN
SET NOCOUNT ON;
 
SET ANSI_WARNINGS OFF;
 
SELECT * FROM dbo.UserData;
END
GO

Compliant solution

CREATE PROCEDURE dbo.SelectUserData
AS BEGIN
SET NOCOUNT ON;
 
/* remove usage of SET ANSI_WARNINGS*/
 
SELECT * FROM dbo.UserData;
END
GO