Subject | Transactions Scope for a Beginner |
---|---|
Author | rajesh69nair |
Post date | 2004-02-23T16:33:28Z |
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
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