Answered by:
Arithmetic overflow error converting expression to data type nvarchar.

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
Vuyiswa Maseko
 Edited by Vuyiswa Maseko Tuesday, March 12, 2013 8:22 PM
Question
Answers

Try this:
CAST(CAST(convert(float,@parPercentageToBeFormatted)* 100 AS decimal(18, 14)) AS nvarchar(20)) + N'%'
 Proposed as answer by Alexandre Matayosi Tuesday, March 12, 2013 8:49 PM
 Marked as answer by Kalman TothModerator Tuesday, March 19, 2013 9:36 PM

This statement will guide you to see what the issue is.
SET NOCOUNT ON; USE tempdb; GO SELECT c1, CAST(c1 AS float) AS c2, CAST(c1 AS float) * 100 AS c3, CAST(CAST(c1 AS float) * 100 AS decimal(5, 1)) AS c4, CAST(CAST(CAST(c1 AS float) * 100 AS decimal(5, 1)) AS nvarchar(7)) AS c5 FROM (VALUES ('43.4703076923077')) AS T(c1); GO
Casting the parameter to [float] and multiplying it by 100 yields a number that at least needs (p, s) equal to (5, 1) to convert it to decimal. If you want to convert it to characters then you will need at least 7 characters to represent also the sign and the decimal separator (4347.0).
AMB
 Marked as answer by Kalman TothModerator Tuesday, March 19, 2013 9:36 PM
All replies

Try this:
CAST(CAST(convert(float,@parPercentageToBeFormatted)* 100 AS decimal(18, 14)) AS nvarchar(20)) + N'%'
 Proposed as answer by Alexandre Matayosi Tuesday, March 12, 2013 8:49 PM
 Marked as answer by Kalman TothModerator Tuesday, March 19, 2013 9:36 PM

This statement will guide you to see what the issue is.
SET NOCOUNT ON; USE tempdb; GO SELECT c1, CAST(c1 AS float) AS c2, CAST(c1 AS float) * 100 AS c3, CAST(CAST(c1 AS float) * 100 AS decimal(5, 1)) AS c4, CAST(CAST(CAST(c1 AS float) * 100 AS decimal(5, 1)) AS nvarchar(7)) AS c5 FROM (VALUES ('43.4703076923077')) AS T(c1); GO
Casting the parameter to [float] and multiplying it by 100 yields a number that at least needs (p, s) equal to (5, 1) to convert it to decimal. If you want to convert it to characters then you will need at least 7 characters to represent also the sign and the decimal separator (4347.0).
AMB
 Marked as answer by Kalman TothModerator Tuesday, March 19, 2013 9:36 PM