Database Reference
In-Depth Information
Avoiding Changing SET Options Within a Stored Procedure
It is generally recommended that you not change the environment settings within a stored procedure and thus
avoid recompilation because the
SET
options changed. For ANSI compatibility, it is recommended that you keep the
following
SET
options
ON
:
ARITHABORT
•
CONCAT_NULL_YIELDS_NULL
•
QUOTED_IDENTIFIER
•
ANSI_NULLS
•
ANSI_PADDINC
•
ANSI_WARNINGS
•
NUMERIC_R0UNDAB0RT
should be
OFF
.
•
And
Although the following approach is not recommended, you can avoid the recompilation caused by some of these
SET
options changes by resetting the options for the connection, as shown in the following modifications to
set.sql
:
IF (SELECT OBJECT_ID('dbo.TestProc')
) IS NOT NULL
DROP PROC dbo.TestProc
GO
CREATE PROC dbo.TestProc
AS
SELECT 'a' + NULL + 'b'; --1st SET CONCAT_NULL_YIELDS_NULL OFF
SELECT 'a' + NULL + 'b'; --2nd
SET ANSI_NULLS OFF
SELECT 'a' + NULL + 'b';
--3rd
GO
SET CONCAT_NULL_YIELDS_NULL OFF;
SET ANSI_NULLS OFF;
EXEC dbo.TestProc;
SET CONCAT_NULL_YIELDS_NULL ON;
--Reset to default
SET ANSI_NULLS ON; --Reset to default
Figure
17-16
shows the Extended Events output.
