Try Catch Transaction rollbacks

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)

Try Catch Transaction rollbacks

Author

Topic

davenaylor2000
Starting Member

3 Posts

Posted - 2007-05-11 : 05:02:46

Hi I am trying to build some inteligent error handling into my stored procedures.

This is an exmaple of one the procedures

BEGIN TRAN
		BEGIN TRY
			IF EXISTS (SELECT ID FROM [dbo1].[RolesTable]
			WHERE id = @ID)
				BEGIN 
					
					DELETE FROM [RolesTable] WHERE ID = @ID
					SET @errorReturnString = '<result errorCode="0"><return>1</return></result>'
					Select @errorReturnString
				END
			ELSE
				SET @errorReturnString = '<result errorCode="1"><return>Role does not exist</return></result>'
				Select @errorReturnString
			END TRY
		BEGIN CATCH
			SET @errorNum = (SELECT ERROR_NUMBER() AS ErrorNumber)
			SET @errorMess = (SELECT ERROR_MESSAGE() AS ErrorMessage)
			SET @errorReturnString = '<result errorCode="1"><return>SQL Error, Number: ' + @errorNum + ' MESSAGE: ' + @errorMess + '</return></result>'
			SELECT @errorReturnString
			IF @@TRANCOUNT > 0
				ROLLBACK TRANSACTION;
		END	CATCH
	IF @@TRANCOUNT > 0
		BEGIN
			COMMIT TRANSACTION;
		END

If the procedure is successfull i get the correct return value in the xml string.

however, if i change the table name on the select to one that doesnt exist. i dont hit the catch which would give me a nice error return string. instead i just get the error returned i.e. table xxx does not exist.

am i missing something?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-11 : 05:04:20

[code]
begin tran
begin try
-- your code here
commit
end try
begin catch
-- your error handling code here
rollback
end catch
[/code]

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

davenaylor2000
Starting Member

3 Posts

Posted - 2007-05-11 : 05:45:43

Hi with the suggestion above i have the following changes to the code

Rolestable1 does not exist btw

BEGIN TRAN
		BEGIN TRY
			IF EXISTS (SELECT ID FROM [dbo].[RolesTable]
			WHERE id = @ID)
				BEGIN 
					
					DELETE FROM [RolesTable1] WHERE ID = @ID
					SET @errorReturnString = '<result errorCode="0"><return>1</return></result>'
					Select @errorReturnString
				END
			ELSE
				BEGIN
					SET @errorReturnString = '<result errorCode="1"><return>Role does not exist</return></result>'
					Select @errorReturnString
				END
			COMMIT TRANSACTION;
			END TRY
		BEGIN CATCH
			SET @errorNum = (SELECT ERROR_NUMBER() AS ErrorNumber)
			SET @errorMess = (SELECT ERROR_MESSAGE() AS ErrorMessage)
			SET @errorReturnString = '<result errorCode="1"><return>SQL Error, Number: ' + @errorNum + ' MESSAGE: ' + @errorMess + '</return></result>'
			SELECT @errorReturnString
			ROLLBACK TRANSACTION;
		END	CATCH

i get the following error when executing the sp

quote:
sg 208, Level 16, State 1, Procedure sa_RemoveRole_sp, Line 29
Invalid object name 'RolesTable1'.
Msg 266, Level 16, State 2, Procedure sa_RemoveRole_sp, Line 29
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

(1 row(s) affected)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-11 : 05:52:33

i had a typo that doesn't have anything to do with your code:
put the begin tran in as the first line of the try block.

i think that your error is obvious

you can't do SELECT ID FROM [dbo].[RolesTable]
if there's no such table. this will result in and error.
you have to check for the existance of the table first:

if object_id('dbo.RolesTable') is null

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources