SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


stored procedure- error


stored procedure- error

Author
Message
SQL Galaxy
SQL Galaxy
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6761 Visits: 3501
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
Roland Alexander STL
Roland Alexander STL
SSC Eights!
SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)

Group: General Forum Members
Points: 984 Visits: 489
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
Jayanth_Kurup
Jayanth_Kurup
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5583 Visits: 1351
Try debugging the procedure
http://www.sqlservercentral.com/articles/SQL+Server+2008/64354/

Jayanth Kurup
Lowell
Lowell
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71116 Visits: 40924
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62135 Visits: 17954
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.

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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search