I am currently using SQL Server2000 - SP4 for an application which opens several connections at the same time. On each connection my application calls stored procedures ONLY (NO single SQL statements) via ADO commands in VC++. Each stored procedure has a structure as follows:
BEGIN TRANSACTION
.....
SELECT STATEMENT WITH UPDLOCK
.....
UPDATE STATEMENT
.....
IF OK
THEN COMMIT
ELSE ROLLBACK.

Now my question is:
If a timeout failure occurs BEFORE the end of the stored procedure (which might have started updating the database), is it correct to switch to a new connection right away ? At times my application hangs (I made sure that NO deadlocks occur !!!) and I wonder whether I should do something before switching to the new connection. E.G.: Is it MANDATORY to rollback soon after the timeout failure because the transaction is in an inconsistent status ?

Any help will be greatly appreciated.

Bye, Antonio