Evaluate Arithmetic String Produces Arithmetic overflow error

Question

First off, we are on Azure SQL DB (now that's said).

We have a calculations table that stores math strings. I'm attempting to get answers from math strings by running them through Exec command.

Simple example EXEC ('select ' + '500*5000') works fine.

EXEC ('select ' + '7500000*10000000000') also works fine.

However

EXEC ('select ' + '75000*1000000') produces Arithmetic overflow error

Huh?
Doesn't the second example also result in a bigint? Why is that one ok?

From what I can tell, there is a background process to dynamically determine the size of the resulting data type. In any case, we need to make sure no calculations error out due to overflow.

My question is how can I force bigint on any evaluation?

I've tried

EXEC ('select cast(' + '750000*1000000' + 'as bigint)')

and

EXEC ('select convert(bigint,' + '750000*1000000' + ')')

Alternatively, is there a setting we can tweak to tell the server to use bigint by default in evaluations?

Dale K · Accepted Answer · 2020-08-19 22:39:27Z

3

You need to carry out your cast/convert to a component of the calculation before you carry out your computation, not after, because SQL Server returns the result of the largest component of the calculation which is why it worked when one of the components provided was already a bigint. e.g.

EXEC ('select ' + '750000*cast(1000000 as bigint)')

-- or

EXEC ('select ' + '750000*convert(bigint,1000000)')

Reference

edited Aug 19, 2020 at 22:39

answered Aug 19, 2020 at 22:31

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael Alexander Over a year ago

"SQL Server returns the result of the largest component of the calculation which is why it worked when one of the components provided was already a bigint" I didn't know that. makes sense. This does give me a direction to go towards. Thanks!

Collectives™ on Stack Overflow

Evaluate Arithmetic String Produces Arithmetic overflow error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related