August 21, 2008 at 12:08 pm
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
May 27, 2011 at 7:16 am
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;
May 27, 2011 at 7:16 am
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???
May 27, 2011 at 10:01 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply