question

acamarks avatar image
acamarks asked

Issues with BigInt when running sql server 2019 on windows server 2019

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?

sql server
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Jeff Moden avatar image
Jeff Moden answered

The real problem is that a million * a million is a billion. Multiply that by as little as 2.18 and all your int calculations will try to produce an int result larger that what an int can be. Try changing the datatype of the input parameters to BIGINT and see if that fixes the problem.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.