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?
