Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
twiz
Starting Member
1 Post |
 Posted - 2005-06-01 : 16:58:45
|
| I am creating an application which will allow the users to type their own formulas in. The formulas are calculations between columns and numeric constants.The problem I'm having is that the user can create an arithmetic overflow, which throws an error in the application.I had a similar problem with converting from nvarchar to float, but I got around it by doing this:(case when IsNumeric(col1)=1 then col1 else null end) * (case when IsNumeric(col2)=1 then col2 else null end) This only multiplies the columns if they are numeric. Has anyone else dealt with the arithmetic overflow problem? |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-06-01 : 17:19:44
|
Since you are allowing users to type in their own formulas, how would you keep them from putting in sometning that causes an overflow?For example:select 2000000000*2causes an overflow.If you are going to allow the users to write their own code, it sounds like this will always be a possibility.quote:
Originally posted by twiz
I am creating an application which will allow the users to type their own formulas in. The formulas are calculations between columns and numeric constants.The problem I'm having is that the user can create an arithmetic overflow, which throws an error in the application.I had a similar problem with converting from nvarchar to float, but I got around it by doing this:(case when IsNumeric(col1)=1 then col1 else null end) * (case when IsNumeric(col2)=1 then col2 else null end) This only multiplies the columns if they are numeric. Has anyone else dealt with the arithmetic overflow problem?
CODO ERGO SUM |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-06-02 : 03:05:15
|
quote:
(case when IsNumeric(col1)=1 then col1 else null end) * (case when IsNumeric(col2)=1 then col2 else null end)
If IsNumeric(col1)=1 is ture and IsNumeric(col2)=1 is false, then you are actually multiplyingcol1*null which makes no senseLook for Convert function in BOLMadhivananFailing to plan is Planning to fail |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2005-06-02 : 13:35:16
|
| Why are you doing math on the database side anyway? let the client handle it.*need more coffee*SELECT * FROM Users WHERE CLUE > 0(0 row(s) affected) |
|
|
|
|
|
|
preventing arithmetic overflow?