Solved

SQL Stored procedure While loop or cursor error trapping

Posted on 2008-10-22
3
2,916 Views
Last Modified: 2012-05-05
Hello experts,

I basically need a example of stored procedure transaction in a while loop or cursor so I can trap errors as it loops but continue with transaction and commit only the ones that are successful.
0
Comment
Question by:krisred
  • 2
3 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22783056
Here is a reference for doing commit/rollback on a transaction level:
http://www.4guysfromrolla.com/webtech/041906-1.shtml

The Try/Catch works in newer versions of SQL, the @@Error approach will work in SQL 2000.

You would just do something like this:
WHILE (someconition)

BEGIN
 

BEGIN TRANSACTION

-- other code here based on version, see link

-- either commit or rollback, but when done goes to next in loop
 

END

Open in new window

0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 22783066
Using a cursor the logic flows the same.
DECLARE yourcursor CURSOR FOR ...

 

OPEN yourcursor

 

FETCH NEXT FROM yourcursor

 

WHILE @@FETCH_STATUS = 0

BEGIN

    BEGIN TRANSACTION

    -- commit or rollback
 

    FETCH NEXT FROM yourcursor

END 

 

CLOSE yourcursor

DEALLOCATE yourcursor

Open in new window

0
 
LVL 11

Assisted Solution

by:Louis01
Louis01 earned 250 total points
ID: 22783523
Take note of the CURSOR_CLOSE_ON_COMMIT and XACT_ABORT.
--If you use a cursor:

SET CURSOR_CLOSE_ON_COMMIT OFF;
 

-- When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting.

SET XACT_ABORT OFF;
 

WHILE @Variable1 <= @Variable2

 BEGIN

	BEGIN TRANSACTION

	BEGIN TRY

		BEGIN TRANSACTION;

		-- Your transaction here

		COMMIT TRANSACTION;

	END TRY

	BEGIN CATCH

		ROLLBACK TRANSACTION;

	END CATCH;
 

  SET @Variable2 = @Variable2 + 1;

 END;

Open in new window

0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
grouping logic 6 46
SQL Query resolving a string conversion issue 26 37
SQL Field Length for Email Address 3 15
MS SQL Pivot table help 4 5
Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
This article discusses four methods for overlaying images in a container on a web page
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now