Reagrding Procedures

Analytics
Enthusiast

Reagrding Procedures

Dear TD Masters,

I have the below procedure :

REPLACE PROCEDURE ProcedureName
BEGIN

DECLARE stmt VARCHAR(1024);

SET stmt = 'REPLACE VIEW DatabaseX.ViewA AS LOCK ROW FOR ACCESS'
' SELECT * FROM DatabaseY.TableA;
COMMIT; CALL dbc.SysExecSQL(:stmt);

SET stmt = 'REPLACE VIEW DatabaseX.ViewB AS LOCK ROW FOR ACCESS'
' SELECT * FROM DatabaseY.TableB;
COMMIT; CALL dbc.SysExecSQL(:stmt);

END;

My requirement is like when I execute this procedure,either both the stmt gets executed or none.
If while executing the 2nd stmt,my procedure fails...the Replace view of the 1st stmt also gets reverted back to its original.

How can I achieve this within a procedure?

Regards,
Ansh
1 REPLY
Enthusiast

Re: Reagrding Procedures

Guys, did any one of you have solution to my above problem..its really urgent..Thanks...