-2

I'm trying to replicate the Java int overflow / underflow logic in SQL (SQL Server 2016). What would the logic be for:

(a * b) + c

where a, b, and c can range from -2147483648 to 2147483648 (the range of an int in SQL)

For example, in java if you calculate (135 * 12) + 2147483647, the result is -2147482029. In SQL, for an int, it'll error with an overflow. I need to replicate the java overflow / underflow logic in SQL, so it'll work the same and produce an int.

tostao
  • 2,803
  • 4
  • 38
  • 61
Pops
  • 468
  • 2
  • 15
  • 3
    Take a look at https://stackoverflow.com/questions/52405042/java-int-overflow-logic-in-sql. Any difference? – jarlh Sep 19 '18 at 13:29
  • [How I can detect integer overflow on 32 bits](//stackoverflow.com/q/21233582) – 001 Sep 19 '18 at 13:29
  • [Reproduce behavior MAX_VALUE and MIN_VALUE](https://stackoverflow.com/questions/46421099/reproduce-behavior-max-value-and-min-value) Note sure if the accepted answer is useful however, since I doubt SQL has the same bitwise operations. – Kevin Cruijssen Sep 19 '18 at 13:30
  • Guava's `IntMath` class provides methods called things like [`checkedAdd`](https://google.github.io/guava/releases/19.0/api/docs/com/google/common/math/IntMath.html#checkedAdd(int,%20int)), which throws an exception if overflow occurs. – Andy Turner Sep 19 '18 at 13:49
  • Why are you porting code into SQL Server from Java? They're not usually thought of as similar. If this is from e.g. Oracle "Java Stored Procedures" then you might want to consider using CLR procedures, where C# has similar behaviour to Java in this regard (although you shouldn't use CLR if this is the *entire* piece of Java you're porting) – Damien_The_Unbeliever Sep 19 '18 at 14:12

1 Answers1

0

I figured it out

declare @result bigint

set @result = ((@a* @b) + @c) % 4294967296
IF @result >= 2147483648 SET @result = @result - 4294967296
IF @result < -2147483648 SET @result = @result + 4294967296
Pops
  • 468
  • 2
  • 15