Transactions in a stored procedure

[gauthee]

Hi, I have a sp where in few values would be passed as input parameters, now using these values insertion opertion should be done for 11 tables. Now if anyone insert statement fails it need not perform the remaining insert statements. What i've done: begin transaction insert insert insert insert... .... ... 11statements total if(@@error<>0) rollback transaction else commit transaction Is this correct! this is leading to some problems, like other users if trying to use any of the tables i used it is giving errors

Gautham

[joon vh]

it seems correct to me, I always use

	IF @@ERROR <> 0
	BEGIN
		ROLLBACK TRANSACTION TRANS
	END

	COMMIT TRANSACTION TRANS

---

Visual Studio can't evaluate this, can you? public object moo { __get { return moo; } __set { moo = value; } }

[andyharman]

The value of @@ERROR is reset after every SQL statement. You should either use the new SQL-Server 2005 Try-Catch mechanism, or have checks on @@ERROR after each of your insert statements (in which case I would normally use a GOTO to jump to a rollback at the end of the procedure). What errors are you getting?