Problem

Have you ever considered how ANSI_WARNINGS and ANSI_NULL affects your store procedure? In this article, I’m going to explain which differences when we use those options with a store procedure and an ad-hoc query.

ANSI_WARNINGS

Definition

ANSI_WARNINGS control ISO behavior various error conditions. It raises a warning error if it is ON following up several below situations. And  I’m trying to use the option with an ad-hoc query and a store procedure to see how it controls?

  • A NULL value appears in aggregation functions such as  SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP, or COUNT.
  • Divide-by-zero and arithmetic overflow errors.
  • String truncation.

To make it simple, I will try to insert data into a table with exceeding data length of a column, because of which I want to receive a string truncation error. The first exam, I will set ANSI_WARNINGS ON and the second I will set OFF.

(Source: https://msdn.microsoft.com/en-us/library/ms190368(v=sql.105).aspx)

Ad-hoc query

USE Tempdb;
GO
DECLARE @ansi_warnings bit;
SELECT @ansi_warnings= is_ansi_warnings_on FROM sys.databases
WHERE name='tempdb';

IF @ansi_warnings = 0
 PRINT 'ANSI_WARNINGS is OFF';
GO
--- Enable ANSI_WARNINGS
SET ANSI_WARNINGS ON;
GO
DECLARE @ansi_warning TABLE(name varchar(5))
-- Insert data with ANSI_WARNINGS ON
INSERT INTO @ansi_warning VALUES('ABCDEFG')
GO

--- Disable ANSI_WARNINGS
SET ANSI_WARNINGS OFF;
GO
DECLARE @ansi_warning TABLE(name varchar(5))
-- Insert data with ANSI_WARNINGS OFF
INSERT INTO @ansi_warning VALUES('ABCDEFG')

SELECT * FROM @ansi_warning
Go

The  result of the first insert, error (Msg 8152) is raised while the second was run successfully.

String truncation error

Store procedure

Now, I will use this option while passing a parameter to store procedure.

IF OBJECT_ID(N'InsertData') IS NOT NULL
DROP PROCEDURE InsertData
GO
CREATE PROCEDURE InsertData
(
@value varchar(5)
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ansi_warning TABLE(name varchar(5))
INSERT INTO @ansi_warning VALUES(@value)
SELECT * FROM @ansi_warning
END
GO
DECLARE @ansi_warnings bit;
SELECT @ansi_warnings= is_ansi_warnings_on FROM sys.databases
WHERE name='tempdb';

IF @ansi_warnings = 0
PRINT 'ANSI_WARNINGS is OFF';
GO
-- Insert data with ANSI_WARNINGS ON
SET ANSI_WARNINGS ON;
GO
EXEC dbo.InsertData @value='ABCDEF';

-- Insert data with ANSI_WARNINGS ON
SET ANSI_WARNINGS OFF;
GO
EXEC dbo.InsertData @value='ABCDEF';

Obviously, you see no string truncation error.

ANSI_NULLS

Specifies ISO behavior of Equal (=) and Not Equal (<>) comparison operators when they are used with null values. As same as ANSI_WARNINGS, I’m trying to use the option with an ad-hoc query and a store procedure.

When ANSI_NULLS ON, a SELECT statement that uses WHERE column = NULL returns zero rows even if there are null values. WHERE column <> NULL returns also zero rows even if there are nonnull values.

When ANSI_NULLS OFF, a SELECT statement that uses WHERE column = NULL returns the rows have null values. WHERE column <> NULL returns also the rows have nonnull values.

When ANSI_NULL ON, all comparisons against a null value evaluate UNKNOWN.  When SET ANSI_NULLS is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL. If SET ANSI_NULLS is not specified, the setting of the ANSI_NULLS option of the current database applies

(Source: https://msdn.microsoft.com/en-us/library/ms188048(v=sql.105).aspx)

 

Ad-hoc query

BEGIN
DECLARE @AnsiNulls TABLE (ID int, Value varchar(50))
INSERT INTO @AnsiNulls
VALUES(1,'ABC'),(NULL,'DEF')
-- Select with ANSI_NULLS ON
SET ANSI_NULLS ON;
SELECT * FROM @AnsiNulls WHERE ID = NULL

-- Select with ANSI_NULLS OFF
SET ANSI_NULLS OFF;
SELECT * FROM @AnsiNulls WHERE ID = NULL

END

No returned record in the first query but there is one record in the second

ANSI_NULLS with adhoc query

 

Store procedure

IF OBJECT_ID(N'AnsiNulls_Test') IS NOT NULL
DROP PROCEDURE AnsiNulls_Test
GO
CREATE PROCEDURE AnsiNulls_Test
AS
BEGIN
DECLARE @AnsiNulls TABLE (ID int, Value varchar(50))
INSERT INTO @AnsiNulls
VALUES(1,'ABC'),(NULL,'DEF')
SELECT * FROM @AnsiNulls WHERE ID = NULL

END
GO
-- Select with ANSI_NULLS ON

SET ANSI_NULLS ON;

EXEC AnsiNulls_Test

 

-- Select with ANSI_NULLS OFF

SET ANSI_NULLS OFF;

EXEC AnsiNulls_Test

ANSI_NULLS does not affect the store procedure in this case. However, I tried to enable/disable ANSI_NULLS before creating my store procedure. Then it controled as same as behavior of above adhoc query.

SET ANSI_NULLS ON;
GO
IF OBJECT_ID(N'AnsiNulls_Test') IS NOT NULL
DROP PROCEDURE AnsiNulls_Test
GO
CREATE PROCEDURE AnsiNulls_Test
AS
BEGIN
DECLARE @AnsiNulls TABLE (ID int, Value varchar(50))
INSERT INTO @AnsiNulls
VALUES(1,'ABC'),(NULL,'DEF')
SELECT * FROM @AnsiNulls WHERE ID = NULL

END
GO
-- Select with ANSI_NULLS ON

EXEC AnsiNulls_Test

No record is returned.

SET ANSI_NULLS OFF;
GO
IF OBJECT_ID(N'AnsiNulls_Test') IS NOT NULL
DROP PROCEDURE AnsiNulls_Test
GO
CREATE PROCEDURE AnsiNulls_Test
AS
BEGIN
DECLARE @AnsiNulls TABLE (ID int, Value varchar(50))
INSERT INTO @AnsiNulls
VALUES(1,'ABC'),(NULL,'DEF')
SELECT * FROM @AnsiNulls WHERE ID = NULL

END
GO
-- Select with ANSI_NULLS OFF

EXEC AnsiNulls_Test

One record is returned

CONCLUSION

  • When you develop a store procedure, you should be careful to determine the setting of ANSI_WARNINGS and ANSI_NULLS. Also, when you deploy the script of your store procedures, consider generate the settings.
  • ANSI_NULL defined inside a store procedure, does not affect the store procedure.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s