Solved

SQL Stored procedure While loop or cursor error trapping

Posted on 2008-10-22
3
2,919 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

810 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