Arithmetic overflow error converting int to data type numeric in sp_executesql usage

Question

Hi I am trying to obtain some calculated values which are decimals as shown below-

BEGIN
 declare @b_server_qty int,
 @b_bookings numeric(30,4),
 @b_std_margin numeric(30,4),
 @b_avg_sp numeric(30,4),
 @b_avg_discount numeric(30,4),
 @c_server_qty int,
 @pos_ucs_qty int,
 @qry_B_server_qty nvarchar(max)='',
 @qry_C_server_qty nvarchar(max)='',
 @paramDefinition nvarchar(max)

 SELECT @qry_B_server_qty = N'SELECT @b_server_qtyOUT =        sum(server_qty),@b_bookingsOUT = SUM(product_bookings_net)/1000000,
    @b_std_marginOUT = isnull(((sum(product_bookings_net)-sum(actaul_product_cost))/nullif(sum(product_bookings_net),0)),0),
    @b_avg_spOUT = isnull((sum(product_bookings_net)/nullif(sum(server_qty),0)),0),
    @b_avg_discountOUT = isnull(((sum(product_bookings_base_list)-sum(product_bookings_net))/nullif(sum(product_bookings_base_list),0)),0) 
    FROM UCS_Bizz_PRODUCT_DATA 
    WHERE '+@common_Where_clause+' AND major_line_product_family = ''UCSB'' 
    OR (major_line_product_family = ''BKG_ADJ_PF'' AND product_family=''UCSB'')';
 SET @paramDefinition = N'@b_server_qtyOUT INT OUTPUT, @b_bookingsOUT numeric(30,4) OUTPUT, 
 @b_std_marginOUT numeric(30,4) OUTPUT,@b_avg_spOUT numeric(30,4) OUTPUT,@b_avg_discountOUT numeric(30,4) OUTPUT';
 EXEC sp_executesql @qry_B_server_qty,@paramDefinition,@b_server_qtyOUT=@b_server_qty OUTPUT,
      @b_bookingsOUT =@b_bookings OUTPUT, @b_std_marginOUT =@b_std_margin OUTPUT,@b_avg_spOUT =@b_avg_sp OUTPUT,
      @b_avg_discountOUT=@b_avg_discount OUTPUT;

 SELECT @b_std_margin,@b_avg_sp,@b_avg_discount,@b_bookings

The values are calculated properly

(SELECT @b_std_margin,@b_avg_sp,@b_avg_discount,@b_bookings

this statement prints values correctly after procedure execution) but still I get the error-

"Arithmetic overflow error converting int to data type numeric".

How can I fix the issue? I have verified, the length of my calculated fields are for sure not more than 30 digits.

Solution

The error typically occurs when we pass a numeric value that is too large. If we declare a numeric(5,2) variable, it can only hold three digits before the decimal point.

The following statements works fine in SQL Server:

DECLARE @n NUMERIC(5,2)
SET @n = 123.123456

However, we will encounter the same error when set the variable value as follows:

DECLARE @n NUMERIC(5,2)
SET @n = 1234.123456