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


how to log error in middle of the cursor records


how to log error in middle of the cursor records

Author
Message
blnbmv
blnbmv
Old Hand
Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)

Group: General Forum Members
Points: 326 Visits: 114
hi members,

I need help to make a cursor in a stored procedure to make it skip to the next id if that particular row has an error. and also I want to display the error number,error message and also for which record that error came in the database and record this error number,error message and also the record details into some table.

I wanted only through cursor as I know the below scenario can be done through set based sql and also through Common table expressions. But I want through cursor and also

My cursor want to increase sal by 100 to each empno.

Eg: empno ename sal
10 xxx 1000
20 yyy 2000
30 zzz 3000


errortable

errorno errormessage empno ename sal
20 some error 20 yyy 2000


If anyone can give with example it would be great.


Thanks
sai
Jogos
Jogos
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 127
An example of a cursor you can find in this post http://www.sqlservercentral.com/articles/cursors/65136/, but don't forget to read the discution for there is some discution possible wether it is a good thing to use cursor.

For the error-handling
-- Error-declarations --
DECLARE @MyErrNumber int,
@MyErrSeverity int,
@MyErrState int,
@MyErrLine int,
@MyErrProcedure nvarchar(128),
@MyErrMessage nvarchar(4000);

SET @MyErrNumber = 0
--

BEGIN TRY
-- execution (begin)
-- execution (end)
END TRY
BEGIN CATCH
SELECT @MyErrNumber = ERROR_NUMBER(),
@MyErrSeverity = ERROR_SEVERITY(),
@MyErrState = ERROR_STATE(),
@MyErrLine = ERROR_LINE (),
@MyErrProcedure = ERROR_PROCEDURE(),
@MyErrMessage = ERROR_MESSAGE()

select @MyErrNumber As ErrNumber
,@MyErrSeverity As ErrSeverity
,@MyErrState As ErrState
,@MyErrLine As ErrLine
,@MyErrProcedure As ErrProcedure
,@MyErrMessage As ErrMessage ;

-- close open transactions (only valid for transactions in try-block)
IF @@TRANCOUNT > 1
BEGIN
ROLLBACK TRAN
END
-- DO something with error-values here

END CATCH;


Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)

Group: General Forum Members
Points: 116912 Visits: 9672
blnbmv (8/21/2008)
hi members,

I need help to make a cursor in a stored procedure to make it skip to the next id if that particular row has an error. and also I want to display the error number,error message and also for which record that error came in the database and record this error number,error message and also the record details into some table.

I wanted only through cursor as I know the below scenario can be done through set based sql and also through Common table expressions. But I want through cursor and also

My cursor want to increase sal by 100 to each empno.

Eg: empno ename sal
10 xxx 1000
20 yyy 2000
30 zzz 3000


errortable

errorno errormessage empno ename sal
20 some error 20 yyy 2000


If anyone can give with example it would be great.


Thanks
sai


Why do you think you need a cursor???
Sean Lange
Sean Lange
SSC Guru
SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)

Group: General Forum Members
Points: 113274 Visits: 18297
Why use a simple statement like
update myTable set Salary = salary + 100?

when a horribly slow and bloated cursor approach will take exponentially longer to run?

_______________________________________________________________

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