Possible to detect when arithmetic overflow would occur before POWER(x,y) is executed?-CodePudding

Example:

-- inputs
declare @x decimal(28,10) = 10001.0 
declare @y decimal(18,6) = 7.0

-- later on, inside a udf
select POWER(@x, @y)

Result:

Msg 8115, Level 16, State 6, Line 13
Arithmetic overflow error converting float to data type numeric.

I understand why the overflow is occurring. My question is, is it possible to detect, just before POWER is executed, whether the overflow would occur? Note that the code is run inside a UDF, so cannot use TRY...CATCH. If I can detect it in advance, I can take avoiding action (e.g. return NULL for the result, which is suitable for my requirements).

CodePudding user response：

You could use Try-Catch or you could use a formula to predict the output digits and return NULL instead. Formula to predict number of digits from here

Predict Number of Digits of Power Function

Declare @Num DECIMAL(28,10) = 10001
    ,@Exponent DECIMAL(28,10) = 7
    ,@NumOfDigits INT

/*Predict number of digits from power function*/
SELECT @NumOfDigits = FLOOR(1   @exponent* CAST(LOG(@Num,10) AS DECIMAL(38,10)))

SELECT 
    CASE WHEN @NumOfDigits <= 38 /*Max decimal precision, return type from POWER function according to https://docs.microsoft.com/en-us/sql/t-sql/functions/power-transact-sql?view=sql-server-ver15*/ 
              - 10 /*Scale of @Num. Need to leave enough digits to record decimal places*/
        THEN POWER(@Num,@Exponent) /*If less than or equal to precision, return value*/
    ELSE NULL /*If outside precision, just returns NULL. Could update to return something else*/
END