Subject Transactions Scope for a Beginner
Author rajesh69nair
Hi All,

I am a beginner at this Database ..I wanted to incorporate a
transaction scope into my stored procedure. Sample script. If someone
could identify with the error. Im getting Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 9, char 13.
SET.


Below is the stored procedure...




CREATE PROCEDURE UPDATE_DEPT_MODIFY
AS
DECLARE VARIABLE DEPT_ID INTEGER;
BEGIN
FOR SELECT TB001_MR_DEPARTMENT.FD001_ID FROM
TB001_MR_DEPARTMENT INTO :DEPT_ID
DO
BEGIN

SET TRANSACTION UPDATE_DEPT;


UPDATE TB001_MR_DEPARTMENT SET
TB001_MR_DEPARTMENT.FD001_MODIFY_BY=:DEPT_ID
WHERE TB001_MR_DEPARTMENT.FD001_ID = :DEPT_ID;

IF (DEPT_ID =2)
THEN
BEGIN
COMMIT TRANSACTION UPDATE_DEPT;

END
ELSE
BEGIN
ROLLBACK TRANSACTION UPDATE_DEPT;
END


END

END