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

how to log error in middle of the cursor records Expand / Collapse
Author
Message
Posted Thursday, August 21, 2008 12:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 15, 2009 12:57 PM
Points: 32, 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
Post #556819
Posted Friday, May 27, 2011 7:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 19, 2013 10:11 AM
Points: 17, Visits: 126
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;

Post #1116215
Posted Friday, May 27, 2011 7:16 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:52 AM
Points: 21,385, Visits: 9,601
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???
Post #1116216
Posted Friday, May 27, 2011 10:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 13,083, Visits: 11,918
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 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 #1116368
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse