July 28, 2012 at 5:45 am
Hi,
I have attached two files regarding execute SP at sql server 2008 databases,... It was working fine with SQL 2000 database.
First time exec SP is working fine, second time throwing error
Please tell me, what is the worng with SP?
Error messages
Arithmetic Overflow error converting numeric datatype to numeric
rgds
ananda
July 31, 2012 at 2:16 pm
An overflow error indicates that the number you're trying to stuff into a column or variable is too large for the column or variable's data type. For example:
DECLARE @tiny tinyint;
SET @tiny = 999;
Running this results in an overflow error. Find the numeric data type in your table and figure out why the value you're trying to put in there is too large, and you'll solve the problem.
Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
July 31, 2012 at 2:22 pm
Try debugging the procedure
http://www.sqlservercentral.com/articles/SQL+Server+2008/64354/
July 31, 2012 at 2:25 pm
Mr. Popki nailed it;
your excellent error message shows the value 2012061414297 is being inserted into a table;
that value is larger than the max of an int:
select 2012061414297
select convert(int,2012061414297)
(1 row(s) affected)
Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type int.
Lowell
July 31, 2012 at 2:45 pm
The reason for the error has been identified.
Please tell me, what is the worng with SP?
However nobody really addressed this question. Honestly I see a lot of things not necessarily wrong but far from optimal in here.
select * from [a number of table valued functions] You should avoid select * in code, it is fine for testing but NOT fine in production.
SplitString function is a potential for performance issue depending on how it is coded. Given the mass of cursors in the rest of this I would suspect this function is using a while loop?
c1 cursor. This looks like you could use STUFF instead of a cursor. More select *
In general I think you will find that in the future your naming conventions will cause you grief. They don't have anything that help identify what it is/does.
c2 cursor. This again looks like you could use STUFF instead of the cursor. More select *. the big change here would be to convert GetDataType from a scalar function to an iTVF.
c3 cursor. Another place you could use STUFF. More select *
c4 cursor. Another place you could use STUFF. More select *
Last but not least...you are wide open to sql injection attack. You generate a lot of dynamic sql and then execute it starting around line 154. What happens when somebody passes you 'MyTable; drop database tempdb;--'? Don't try that!!!
I don't think you need any cursors to do this and you need to read up a bit on sql injection and how to properly execute dynamic sql with parameters.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply