Error Handeling in SQL Cursor - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Error Handeling in SQL Cursor

Author

Topic

Snigdha030
Starting Member

13 Posts

Posted - 2007-05-20 : 15:00:46

I am using SQL Server 2000 Cursor for processing some 10,000 data and then instering to another table in a Stored proc. But the prob is for any faulty records it's getting error and hence could not able to proceed with next records.
Though I have used error handeling but still the above prob is there.

Could any one please suggest how can I do the error handeling with in the Stored proc so that if any record will be failed then it will skip that record or write to an error table and proceed with the next record.

Snig.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-20 : 15:16:15

Please do not post in the Article Discussion forum. It's for commenting on existing articles.
Topic moved.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-20 : 15:17:32

why are you using a cursor in the first place?

show us the code you're using. If it's too long then just post relevant part.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Snigdha030
Starting Member

13 Posts

Posted - 2007-05-20 : 15:54:11

Hi here is an example code where only one insert statement is there.
While calculating the value of fld2 for some record (where fld1 value exceed the limit) i m getting arithmatic Over flow. Just to capture those error records in an error table and proceed with other records is there anything we can do.

-------------------------

declare @fld2 int
Set @fld2 = 0

declare c1 cursor for
select fld1 from tabl1

open c1
fetch next from c1 into @fld1

While @@Fetch_status = 0

begin

set @fld2 = @fld2 + @fld1 * @fld1
insert into tabl2 values
(
@fld2
)

If @@error <> 0
begin
Insert into tblerror values(.....)

end

fetch next from c1 into @fld1
end

quote:
Originally posted by Snigdha030

I am using SQL Server 2000 Cursor for processing some 10,000 data and then instering to another table in a Stored proc. But the prob is for any faulty records it's getting error and hence could not able to proceed with next records.
Though I have used error handeling but still the above prob is there.

Could any one please suggest how can I do the error handeling with in the Stored proc so that if any record will be failed then it will skip that record or write to an error table and proceed with the next record.

Snig.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-21 : 00:35:43

Declare the variable @fld2 as bigint and run
For error handling, refer these

http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html

Madhivanan

Failing to plan is Planning to fail

Snigdha030
Starting Member

13 Posts

Posted - 2007-05-21 : 04:20:30

Yeah if I will declare @fld2 as bigint then it will solve the above prob, but thats not what I want.

I want to trap those records in an error table.

quote:
Originally posted by madhivanan

Declare the variable @fld2 as bigint and run
For error handling, refer these

http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources