Arithmetic overflow error converting expression to data type nvarchar.

[vuyiswamb]

Good Day

i have been hit by this error this night.

I have a UDF defined like this

CREATE FUNCTION [dbo].[funcFormatPercentages_Extended_numeric]
(

 -- Add the parameters for the function here

 @parPercentageToBeFormatted nvarchar(20)

) 
RETURNS nvarchar(20)

AS

BEGIN 
	 RETURN CAST(CAST(convert(float,@parPercentageToBeFormatted)* 100 AS decimal(4, 1)) AS nvarchar(5)) + N'%'  
END

so i will call the UDF with the value like this

 
select [dbo].[funcFormatPercentages_Extended_numeric]('-43.4703076923077')

OR

select [dbo].[funcFormatPercentages_Extended_numeric]('36.403813624481')

i get an Error

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type nvarchar.

I tried to change the datatypes and Precision around , but still the error.

Thanks

[TysonLPrice]

I don't know what the effect on the math is but nvarchar(5) is too small. Making it eight or greater will make that work.

[Serge]

That error indicates that the float might not have enough to store the value. It is not a problem with varchar. Use double instead of float (in you cast statement).