In sql server 2019 running on windows server 2019, when I run the function below
SELECT [dbo].[fnDWGetWorkCategoryKey] (1,1,1)
GO
I get the following error
Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type int.
Completion time: 2021-12-21T17:10:35.3872443+11:00
the function is defined as follows
CREATE FUNCTION [dbo].[fnDWGetWorkCategoryKey]
-- Input parameters
(
@Category1Id int,
@Category2Id int,
@Category3Id int
)
RETURNS bigint AS
/*************************************************************************
Returns a unique work category key based on three levels of categorisation
**************************************************************************/
begin
declare @mil bigint
set @mil = 1000000
if @Category1Id > 0 AND @Category2Id > 0 AND @Category3Id > 0
RETURN ((@Category1Id + @mil) * @mil * @mil) + (@Category2Id * @mil) + @Category3Id -- permits a million minus one cat1, cat2, cat3 records (using BigInt)
return -1
end
GO
I've run the same function on sql2008r2 running on windows server 2012,s sql server 2019 on windows server 2016 and the function runs.
The sql 2019 and windows server 2019 combo seems to have a problem with this function.
Has anyone come across this before?
