Rollback looking up SQL Server

[Anakin2112]

I have a stored procedure that duplicates a few tables and I needed to inforce a rollback in the stored procedure if an error occurs during the duplication of tables. The rollback does work when an error occurs, but it then locks up the SQL server and we have to stop the service to unlock SQL server. Here is my code in the stored proc:

BEGIN TRANSACTION

***Duplication code here

IF @@ERROR <> 0

BEGIN
ROLLBACK TRANSACTION
RAISERROR ('An error occured.', 16, 1)
END

ELSE

BEGIN
COMMIT TRANSACTION
END

Any clue to why it is looking the server up? Like I said before it is performing the rollback. Any help will be appreciated. Thanks

 

[SemperFiDownUnda]

First you should not use @@ERROR in a IF statement
put @@ERROR into another varible then look at that variable

@@ERROR gets cleared for EVERY SQL statement executed. Event

IF @@ERROR <> 0

will set @@ERROR back to 0
I'd say your lockup is occuring

somewhere in you &quot;***Duplication code here&quot;

provide the whole script.