how can i rollback transactions in stored procedure if of the query returns error.

If you don't do a commit in the SP, just issue rollback.

i have a procedure like this :


Insert into table1 .....

update table2 ....

Insert into table3 .....

update table4 ....

for some reason Insert into table3 ..... failed (run time error)

when i looked, data was inserted in table1 and updated in table2

i thought if procedure throws run time error, all transcations will be rolled back automatically. I dont not have commit statement in the code.

No, your assumption is not correct. Why should the procedure roll back everything? If you want to have this behavior, set a savepoint at the beginning of the procedure and in case an error occurred, roll back to that savepoint.

Stored procedures are not different in any respect from transaction management. Each procedure call runs in the transactional scope defined by the caller. So whenever the caller commits or rolls back, that will determine what happens with the changes made inside the procedure. (It doesn't matter whether you have auto-commit turned on.) Furthermore, a stored procedure being basically just a container for a sequence of SQL statements can also execute a COMMIT or ROLLBACK. That has an impact on the caller's transaction. There is no autonomous transaction or so...

You can put a rollback statement in a condition handler if that is what you want to happen if a negative return code is encountered. Make sure you don't rollback for warnings like +100.