views:

60

answers:

1

I am using the below sql to import some data from a file from the intranet. However every once a while, there will be a timeout error and the proc would fail, which is why I am using a transaction. If the transaction fails, I want the ImportedTable to get cleared. However this does not seem to happen. Is there anything I am missing here?

ALTER PROCEDURE [dbo].[pr_ImportData] 
 @StoreCode varchar(10),
 @UserId varchar(100)
AS

BEGIN TRANSACTION

-- 1) Clear the data
exec pr_INTRANET_ClearData @StoreCode, @UserId

IF @@ERROR <> 0
BEGIN
 ROLLBACK TRANSACTION
 GOTO EXIT1
END 

-- 2) Add the new data to the history Table
INSERT INTO data_History (...)
SELECT ... from ImportedTable WHERE StoreCode = @StoreCode and UserId = @UserId

IF @@ERROR <> 0
BEGIN
 ROLLBACK TRANSACTION
 GOTO EXIT1
END 

-- 3) Add the data to the live table
INSERT INTO data_Live (...)
SELECT ... from ImportedTable WHERE StoreCode = @StoreCode and UserId = @UserId

IF @@ERROR <> 0
BEGIN
 ROLLBACK TRANSACTION
 GOTO EXIT1
END 

IF @@ERROR <> 0
BEGIN
 ROLLBACK TRANSACTION
 GOTO EXIT1
END 

EXIT1:
-- 4) Delete the rows from the temp table
DELETE FROM ImportedTable WHERE StoreCode = @StoreCode and UserId = @UserId

COMMIT TRANSACTION

Update 1: I am running this against SQL 2000 and SQL2005.

Update 2: To clarify: The ImportedTable never gets cleared at Exit1.

+6  A: 

SET XACT_ABORT ON will make any error to rollback the transaction, removing the need to explicitly rollback in case of error. You should also consider using BEGIN TRY/BEGIN CATCH, as is significantly easier to program than checking for @@ERROR after every statement.

Remus Rusanu
Would that work in SQL2000 and SQL2005?
vikasde
SET XACT_ABORT will work in both. Try/Catch is only for SQL2005 and up.
CodeByMoonlight
Awesome tip. Thanks!
vikasde