Rolling back transactions results in the whole thing being rolled back

See more:

SQL

Hey there all.

I have a little error-handling stored procedure that I'm developing for a client and I'm having a little issue with it; It writes rows to a table, then rolls back executing transactions and returns to the caller. only problem is that it seems to rollback the write of the rows to the table as well. I have committed the transaction before rolling back, is there a way to selectively rollback only the transactions I want to roll back? I've tried removing the rollback code into the outer calling procedure, but to no effect. Here's the code:

USE [DEV_LGT_CM_OneSource]
GO
/****** Object:  StoredProcedure [BizTempMaintNewReporting].[spReportInsert]    Script Date: 02/07/2011 14:21:51 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Monitoring].[spErrorMessage_Insert]') AND type in (N'P', N'PC'))
DROP PROCEDURE [Monitoring].[spErrorMessage_Insert]
GO
/******* Object:  StoredProcedure [BizTempMaintNewReporting].[spReportInsert]    Script Date: 02/07/2011 14:21:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Mel Padden
-- Create date: 2011-02-03
-- Description:	Error Handling procedure to be called from a CATCH block
-- =============================================
CREATE PROCEDURE [Monitoring].[spErrorMessage_Insert](
	@SchemaName			NVARCHAR(4000) -- Must be passed in because it cannot be inferred from runtime context
)
AS
BEGIN
	SET NOCOUNT ON;
	-- Variables to hold the runtime context-derived error messages
	DECLARE @ErrorLine			nvarchar(4000);		-- 
	DECLARE @ErrorMessage		nvarchar(4000);		--
	DECLARE @ErrorNumber		int;				--
	DECLARE @ErrorProcedure		nvarchar(4000);		--
	DECLARE @ErrorSeverity		int;				--
	DECLARE @ErrorState			int;				--
	SELECT @ErrorLine		= ERROR_LINE();
	SELECT @ErrorMessage	= ERROR_MESSAGE();
	SELECT @ErrorNumber		= ERROR_NUMBER();
	SELECT @ErrorProcedure	= @SchemaName + '.[' + ERROR_PROCEDURE() + ']';
	SELECT @ErrorSeverity	= ERROR_SEVERITY();
	SELECT @ErrorState		= ERROR_STATE();
	--SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
	BEGIN TRANSACTION InsertLogMessages;
	-- Log the error data into the exception table
	INSERT INTO [Monitoring].[ErrorMessages](
			[ErrorLine]
		,	[ErrorMessage]
		,	[ErrorNumber]
		,	[ErrorProcedure]
		,	[ErrorSeverity]
		,	[ErrorState]
		,	[Reported]
		,	[Checked])
		 VALUES(
			@ErrorLine
	   ,	@ErrorMessage
	   ,	@ErrorNumber
	   ,	@ErrorProcedure
	   ,	@ErrorSeverity
	   ,	@ErrorState
	   ,	GETDATE()	
	   ,	NULL)	
	COMMIT TRANSACTION InsertLogMessages;
	--	Output the error messages to the console
	PRINT 'Error raised on line:		' + @ErrorLine;
	PRINT 'Error message:				' + @ErrorMessage;
	PRINT 'Error number:				' + CAST(@ErrorNumber as nvarchar(40));
	PRINT 'Error procedure:				' + @ErrorProcedure;
	PRINT 'Error severity:				' + CAST(@ErrorSeverity as nvarchar(40));
	--	Roll back all pending transactions 		
	DECLARE @TransactionCount INT;
	SET @TransactionCount = @@TRANCOUNT
	--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

	
	WHILE @@TRANCOUNT > 0 BEGIN
		ROLLBACK 
	END
	
	--	Print the number of transactions rolled back
	PRINT 'Transaction count:			' + CAST(@TransactionCount as nvarchar(40));
	
END

Thanks in advance for any help!!