CODEWITHSUNDEEP
t-sqlsql-function
Aharon Manne
Aharon Manne

Reputation: 712

SQL Function Error: Arithmetic Overflow

I have a very simple function to calculate and format a floating point number:

ALTER FUNCTION GetUrbanHours 
(
-- Add the parameters for the function here
@driverID int
)
RETURNS int
AS
BEGIN
DECLARE @Result float = 0

SELECT @Result = 
    FORMAT(
        SUM(CONVERT(float, 
            CONVERT(bigint, RouteSummary.UrbanNightSeconds + RouteSummary.UrbanDaySeconds))/(60*60)),
        '##,##0.00')
    FROM RouteSummary WHERE DriverID = @driverID

-- Return the result of the function
RETURN @Result

END

When I call the function with a given parameters, I get the following errors:

Msg 8115, Level 16, State 2, Line 6
Arithmetic overflow error converting expression to data type int.
Msg 8114, Level 16, State 5, Line 9
Error converting data type varchar to float.

When I extract the SELECT statement and print @Result, I get the expected result. What is my problem here?

EDIT

I have rewritten the function as follows:

ALTER FUNCTION GetUrbanHours 
(
@driverID int
)
RETURNS nvarchar(50)
AS
BEGIN
DECLARE @Result nvarchar(50)
DECLARE @SumSeconds bigint
DECLARE @SumHours float

SELECT @SumSeconds = 
    SUM(RouteSummary.UrbanNightSeconds + RouteSummary.UrbanDaySeconds)
    FROM RouteSummary WHERE DriverID = @driverID
IF @SumSeconds != NULL
BEGIN
    SET @SumHours = CONVERT(float, @SumSeconds) / (60*60)
    SET @Result = 
        FORMAT(@SumHours, '##,##0.00')
END
RETURN @Result

END

When I give a parameter that returns rows from the RouteSummary Table, I get NULL. When I give a parameter that returns no rows, I get "Msg 8115, Level 16, state 2, Line 1 Arithmetic overflow error converting expression to data type int." What is wrong now? Also is there a way to identify the line in the function the error message refers to? And why can't I step into the function with the SQL Studio debugger?

Upvotes: 0

Views: 422

Answers (1)

RC_Cleland
RC_Cleland

Reputation: 2304

To solve the first error do not assign a value to the variable. The second error is caused by your use of the TSQL FORMAT statement. The format statement returns a VARCHAR not a FLOAT. See this link for information on the FORMAT command. MSDN link

Upvotes: 0

Related Questions