Home > SQL Server > Ignore arithmetic overflow error – SET ARITHIGNORE – SQL SERVER

Ignore arithmetic overflow error – SET ARITHIGNORE – SQL SERVER


ARITHIGNORE Set statement is used to control the messages raised by arithmetic overflow or divide-by-zero errors during a query execution.

Usage

SET ARITHIGNORE ON
or
SET ARITHIGNORE OFF
 

SET ARITHIGNORE setting only controls whether an error message is returned or not. IF set ON then no message else if set OFF message will be returned. NULL value is returned in both case.

The SET ARITHABORT setting is used to determine whether the query is terminated in the event of arithmetic overflow or divide-by-zero error.

If SET ARITHABORT or SET ARITHIGNORE is OFF and SET ANSI_WARNINGS is ON then SQL Server still returns an error message when encountering divide-by-zero or overflow errors.

These set statement values are initialized by the SQL server while we create the session. So, It is mandatory to set all set statements properly to obtain the required result.

Get the current value of the set statements in the current session using @@OPTIONS.

Example

1. When ARITHABORT and SET ANSI_WARNINGS is OFF,  ARITHIGNORE is ON. Null is returned due to the arithmetic errors.

pic12. When ARITHABORT and SET ANSI_WARNINGS is OFF,  ARITHIGNORE is OFF. Null is returned due to the arithmetic errors and error message is also returned.

pic2

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: