Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

stored procedure- error Expand / Collapse
Author
Message
Posted Saturday, July 28, 2012 5:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:15 AM
Points: 1,015, Visits: 2,843
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


  Post Attachments 
Error_Msg.png (23 views, 29.09 KB)
sp.txt (22 views, 5.39 KB)
Post #1336910
Posted Tuesday, July 31, 2012 2:16 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 7:08 AM
Points: 419, Visits: 326
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 Developing World


There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
Post #1338170
Posted Tuesday, July 31, 2012 2:22 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, December 03, 2013 11:33 PM
Points: 1,789, Visits: 1,013
Try debugging the procedure
http://www.sqlservercentral.com/articles/SQL+Server+2008/64354/


Jayanth Kurup
Post #1338174
Posted Tuesday, July 31, 2012 2:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 12,749, Visits: 31,114
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1338178
Posted Tuesday, July 31, 2012 2:45 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:49 PM
Points: 12,016, Visits: 11,046
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1338190
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse