Transaction not rolling back

I have this stored procedure (greatly simplified for the purposes of the question)
All works fine.
But so I can test the rollback part I alter the SP and intentionally reference a non-existant table.
When I exec the SP it errors - but it also holds open the transaction and I have to kill the spid to release it.

Is the syntax incorrect? The invaid object reference not "caught" or other?
Basically I drop a table so I can recreate it, but I want to rollback the DROP table if there is an issue with the SELECT INTO part of the SP

ALTER PROCEDURE [dbo].[abc]
as 
BEGIN TRANSACTION
	IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[xxx]') AND type in (N'U'))
		DROP TABLE [dbo].[xxx]
	BEGIN TRY
		select SomeStuff
		into xxx
		from zzz
	COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
	END CATCH

Open in new window

Microsoft SQL ServerMicrosoft SQL Server 2008

Last Comment

QPR

8/22/2022 - Mon

Harish Varghese

5/13/2014

The Try/Catch block will not be able to detect any COMPILE errors which includes schema errors.
To be able to CATCH such errors, we should wrap the statements in dynamic SQL in TRY block.

ALTER PROCEDURE [dbo].[abc]
as 
	BEGIN TRANSACTION
	IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[zzz]') AND type in (N'U'))
		DROP TABLE [dbo].[zzz]
	BEGIN TRY
		Declare @sql nvarchar(4000)
		SET @sql = 'select SomeStuff into testdrop from zzz'
		EXEC sp_executesql @sql
	COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		Select 'Rolling back'
		ROLLBACK TRANSACTION
	END CATCH

Open in new window

Thanks to http://www.codeproject.com/Articles/12080/Using-TRY-CATCH-in-Transact-SQL

ASKER CERTIFIED SOLUTION

Harish Varghese

5/13/2014

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Scott Pletcher

5/14/2014

SQL can't trap certain errors, and a non-existent table is one of them.

SQL does verify the schema of existing objects in CATCH blocks, but allows undefined objects to be accepted ("deferred resolution"). That is, if you reference a non-existent column in an existing table, you will get a proc alter/create error, but not if the table does not exist at all.

I think you can specify:
SET XACT_ABORT ON

then divide by 0:
SELECT 1/0
to force an error.

Btw, you should add:
SET NOCOUNT ON
immediately at the start of the proc:
ALTER PROCEDURE [dbo].[abc]
as
SET NOCOUNT ON;
...rest_of_code_as_before...

QPR

5/14/2014

ASKER

Thanks Scott, that's what I suspected (not caught) and yep set nocount is on, I just trimmed the sp for readability

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

James Murphy

QPR

5/14/2014

ASKER

Parent (so to speak) sproc did the trick - thanks