2

This might be fundamentally stupid, but here goes:

I have a stored procedure that I need to just run without throwing overflow errors. In case of an overflow, I'm happy with a NULL.

I see it suggested all around the internet (eg. here and here) to just use

SET ARITHABORT OFF;
SET ARITHIGNORE ON;

That is fine, but should I, like, turn them back on at the end of my SP?

SET ARITHABORT ON;
SET ARITHIGNORE OFF;

Like this? Are these even the defaults?

Also, Do I need both switches to get it to ignore overflow errors (in my case, trying to put '606006000' in a SMALLINT field)?

Using Azure DB v12.

Community
  • 1
  • 1
vacip
  • 5,246
  • 2
  • 26
  • 54
  • Shouldn't need to. https://msdn.microsoft.com/en-us/library/ms190306.aspx indicates ARITHABORT should always be set to ON on Logon session, "You should always set ARITHABORT to ON in your logon sessions." implying that its session specific. better clarifications: https://msdn.microsoft.com/en-us/library/ms190356.aspx Note it impacts the SESSION so as long as you're releasing the session before next query and getting a new session, it will be reset automatically – xQbert Jul 05 '16 at 13:55
  • @xQbert Thank you, the second site is a good read. Post this as an answer an I'll accept it. – vacip Jul 05 '16 at 14:01
  • 1
    @xQbert Yep, from your 2nd link: "If a SET statement is run in a stored procedure or trigger, the value of the SET option is restored after control is returned from the stored procedure or trigger." Thank you, my google-fu was weak today. – vacip Jul 05 '16 at 14:02

1 Answers1

1

Some info about these settings.By Default both are true.

SET ARITHIGNORE:
The SET ARITHIGNORE setting only controls whether an error message is returned. SQL Server returns a NULL in a calculation involving an overflow or divide-by-zero error, regardless of this setting

SET ARITHABORT:
The SET ARITHABORT setting can be used to determine whether the query is terminated

Further this is what MSDN has to say about these more..

This setting does not affect errors occurring during INSERT, UPDATE, and DELETE statements. If either SET ARITHABORT or SET ARITHIGNORE is OFF and SET ANSI_WARNINGS is ON, SQL Server still returns an error message when encountering divide-by-zero or overflow errors.

I did some testings and it seems SET ARITHIGNORE OFF setting is only enough for the behaviour you need..

Further you also need to set SET_ANSI_WARNINGS_OFF as well ,other wise this will throw error

SET ARITHABORT OFF
--SET ARITHIGNORE OFF
SET ANSI_WARNINGS OFF

select 1/0

declare @a tinyint
set @a=10000
select @a
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • 1
    Thank you for the thorough explanation. This explains why I couldn't get it to work in the end - I needed ANSI_WARNINGS too. – vacip Jul 06 '16 at 19:01