more questions on try catch w/transcations

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

more questions on try catch w/transcations

Author

Topic

BitShift
Yak Posting Veteran

98 Posts

Posted - 2008-04-10 : 15:47:48

Lets say that within a procedure, you start a transaction. Within the transaction there are three blocks of code, each surrounded by try/catch blocks. The first statement succeeds, but the 2nd fails and doesnt enter its catch block because for example it was inserting into a table that didnt exist at the time the procedure ran. So then, the engine will terminate the procedure and return, but there will still be a transaction open. How can you handle this situation?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 15:50:55

Why don't you run the three statements in a single TRY/CATCH block?

E 12°55'05.25"
N 56°04'39.16"

BitShift
Yak Posting Veteran

98 Posts

Posted - 2008-04-10 : 15:55:03

quote:
Originally posted by Peso

Why don't you run the three statements in a single TRY/CATCH block?

E 12°55'05.25"
N 56°04'39.16"

Duh! Thats a good idea, thank you!

By the way, here is an example of what I was doing, so I suppose my question still stands. However, in this case its probably bad form to open a transaction that is going to span several try/catch blocks...


BEGIN TRANSACTION

	declare @error_number int
	set @error_number = 0	

	--part1
	BEGIN TRY
		--statement here that works
	END TRY
	BEGIN CATCH
		set @error_number = 1
		goto error_handler
	END CATCH

	--part 2
	BEGIN TRY
		--statement here that FAILS
	END TRY
	BEGIN CATCH
		set @error_number = 2
		goto error_handler
	END CATCH

	--part3
	BEGIN TRY
		--statement here that works
	END TRY
	BEGIN CATCH
		set @error_number = 3
		goto error_handler
	END CATCH


	error_handler:
	if @errornumber > 0
	BEGIN
		-- Roll back the transaction and return a non-zero error number
		ROLLBACK		
	END
	RETURN @errornumber



COMMIT

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 16:02:03

Something similar to

BEGIN TRANSACTION

BEGIN TRY

	--part1
	--statement here that works

	--part 2
	--statement here that FAILS

	--part3
	--statement here that works
END TRY

BEGIN CATCH
	ROLLBACK TRANSACTION
END CATCH

IF @@TRANCOUNT > 0
	COMMIT TRANSACTION

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 16:08:55

Or this?

BEGIN TRANSACTION

BEGIN TRY

	--part1
	set @error = 1
	--statement here that works

	--part 2
	set @error = 2
	--statement here that FAILS

	--part3
	set @error = 3
	--statement here that works
END TRY

BEGIN CATCH
	ROLLBACK TRANSACTION
	RETURN @error
END CATCH

IF @@TRANCOUNT > 0
	COMMIT TRANSACTION

RETURN 0

E 12°55'05.25"
N 56°04'39.16"

BitShift
Yak Posting Veteran

98 Posts

Posted - 2008-04-10 : 17:50:39

Well, actually my question still stands. The real problem is if there is an error within the try block that causes the procedure to terminate, yet the catch block is not entered. If a transaction is opened at the very beginning, then it will be orphaned, will it not ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 17:53:20

What error would make the code "terminate" and never enter CATCH block?

E 12°55'05.25"
N 56°04'39.16"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-10 : 18:05:50

Not sure if anyone cares, but my preference is to put the "normal" transactional handling in the TRY and handle the rollback in CATCH. As far as I know both methods work just fine, ie:

BEGIN TRY
	BEGIN TRANSACTION

	--part1
	--statement here that works

	--part 2
	--statement here that FAILS

	--part3
	--statement here that works

	COMMIT TRANSACTION

END TRY

BEGIN CATCH
	ROLLBACK TRANSACTION
END CATCH

BitShift
Yak Posting Veteran

98 Posts

Posted - 2008-04-10 : 18:08:16

Lets say that after the procedure was created, at some point in the future a table was removed that part2 was doing an insert into. That would cause the procedure to terminate without the catch being entered. I think I tested this earlier, but will have to check again.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 18:10:25

Or add more pre-insert handling?

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'xyz')
INSERT xyz (...) values (...)

E 12°55'05.25"
N 56°04'39.16"

BitShift
Yak Posting Veteran

98 Posts

Posted - 2008-04-10 : 18:14:21

quote:
Originally posted by Peso

Or add more pre-insert handling?

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'xyz')
INSERT xyz (...) values (...)

E 12°55'05.25"
N 56°04'39.16"

Yes, that should do it. Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources