SET NUMERIC_ROUNDABORT (T-SQL)

Specifies the level of error reporting generated when rounding in an expression causes a loss of precision.

Syntax

SET NUMERIC_ROUNDABORT {ON | OFF}

Remarks

When SET NUMERIC_ROUNDABORT is ON, an error is generated when a loss of precision occurs in an expression. When OFF, losses of precision do not generate error messages and the result is rounded to the precision of the column or variable storing the result.

Loss of precision occurs when attempting to store a value with a fixed precision in a column or variable with less precision.

If SET NUMERIC_ROUNDABORT is ON, SET ARITHABORT determines the severity of the generated error. This table shows the effects of these two settings when a loss of precision occurs.


Setting
SET NUMERIC_

ROUNDABORT ON

SET NUMERIC_

ROUNDABORT OFF

SET ARITHABORT ON Error is generated; no result set returned. No errors or warnings, result is rounded.
SET ARITHABORT OFF Warning is returned; expression returns NULL. No errors or warnings, result is rounded.

The setting of SET NUMERIC_ROUNDABORT is set at execute or run time and not at parse time.

Permissions

SET NUMERIC_ROUNDABORT permissions default to all users.

Examples

This example shows two values with a precision of four decimal places that are added and stored in a variable with a precision of two decimal places. The expressions demonstrate the effects of the different SET NUMERIC_ROUNDABORT and SET ARITHABORT settings.

-- SET NOCOUNT to ON,

-- SET NUMERIC_ROUNDABORT to ON, and SET ARITHABORT to ON.

SET NOCOUNT ON

PRINT 'SET NUMERIC_ROUNDABORT ON'

PRINT 'SET ARITHABORT ON'

SET NUMERIC_ROUNDABORT ON

SET ARITHABORT ON

GO

DECLARE @result decimal(5,2),

@value_1 decimal(5,4), @value_2 decimal(5,4)

SET @value_1 = 1.1234

SET @value_2 = 1.1234

SELECT @result = @value_1 + @value_2

SELECT @result

GO

-- SET NUMERIC_ROUNDABORT to ON and SET ARITHABORT to OFF.

PRINT 'SET NUMERIC_ROUNDABORT ON'

PRINT 'SET ARITHABORT OFF'

SET NUMERIC_ROUNDABORT ON

SET ARITHABORT OFF

GO

DECLARE @result decimal(5,2),

@value_1 decimal(5,4), @value_2 decimal(5,4)

SET @value_1 = 1.1234

SET @value_2 = 1.1234

SELECT @result = @value_1 + @value_2

SELECT @result

GO

-- SET NUMERIC_ROUNDABORT to OFF and SET ARITHABORT to ON.

PRINT 'SET NUMERIC_ROUNDABORT OFF'

PRINT 'SET ARITHABORT ON'

SET NUMERIC_ROUNDABORT OFF

SET ARITHABORT ON

GO

DECLARE @result decimal(5,2),

@value_1 decimal(5,4), @value_2 decimal(5,4)

SET @value_1 = 1.1234

SET @value_2 = 1.1234

SELECT @result = @value_1 + @value_2

SELECT @result

GO

-- SET NUMERIC_ROUNDABORT to OFF and SET ARITHABORT to OFF.

PRINT 'SET NUMERIC_ROUNDABORT OFF'

PRINT 'SET ARITHABORT OFF'

SET NUMERIC_ROUNDABORT OFF

SET ARITHABORT OFF

GO

DECLARE @result decimal(5,2),

@value_1 decimal(5,4), @value_2 decimal(5,4)

SET @value_1 = 1.1234

SET @value_2 = 1.1234

SELECT @result = @value_1 + @value_2

SELECT @result

GO

  

See Also
Data Types SET
SET ARITHABORT  

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.