Avatar of QPR
QPR
Flag for New Zealand asked on

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

Avatar of undefined
Last Comment
QPR

8/22/2022 - Mon
Harish Varghese

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

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

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

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

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