1

The following code gives me this error:

Arithmetic overflow error converting expression to data type int.

select CONCAT(
    round( convert(varbinary, newid()) * 256, 0 ), '.',
    round( convert(varbinary, newid()) * 256, 0 ), '.',
    round( convert(varbinary, newid()) * 256, 0 ), '.',
    round( convert(varbinary, newid()) * 256, 0 )) SenderIP

How can I work around this?

David Klempfner
  • 8,700
  • 20
  • 73
  • 153
  • Very good question. – The Impaler Jan 18 '21 at 01:10
  • 1
    `NewId()` returns a [`uniqueidentifier`](https://docs.microsoft.com/en-us/sql/t-sql/data-types/uniqueidentifier-transact-sql?view=sql-server-ver15), a 16-byte value. You `convert` that to a `VarBinary(30)`, then attempt to multiply it by `256`. That forces an implicit conversion to `int` which truncates the value. `* 256` overflows. Aside: The best practice is to always supply a length for `var` types. [`Cast` and `Convert`](https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15#arguments) default to `30`, otherwise the default length is `1`. – HABO Jan 18 '21 at 03:32
  • 1
    `Cast( Cast( NewId() as VarBinary(30) ) as Int ) & 0xFF` will give you non-negative values and, if repeated in a single query, return a different value for each expression. – HABO Jan 18 '21 at 03:38

0 Answers0