Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


stored procedure- error


stored procedure- error

Author
Message
SQL Galaxy
SQL Galaxy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1220 Visits: 3465
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
Mr or Mrs. 500
Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)

Group: General Forum Members
Points: 520 Visits: 384
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
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

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

Jayanth Kurup
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14980 Visits: 39002
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16648 Visits: 17028
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)
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