Create Stored procedure with multiple sql statements

Question

Louie McLaughlin · Mar 6, 2023

I need a stored procedure where I can execute multiple delete from statements.

This will work to create a stored procedure, but when I add in other delete from, get error invalid sql statements when try to create

CREATE PROCEDURE DeleteSpecimenTwo
(IN Specno VARCHAR(50))
BEGIN
DELETE FROM PCONT WHERE PSPECNO = :Specno;
END;

This doesn't work

CREATE PROCEDURE DeleteSpecimenTwo
(IN Specno VARCHAR(50))
BEGIN
DELETE FROM PCONT WHERE PSPECNO = :Specno;

DELETE FROM PSPEC WHERE PSPECNO = :Specno;
END;

Any suggestions

Thanks

Louie

CREATE PROCEDURE DeleteSpecimenTwo (IN Specno VARCHAR(50)) LANGUAGE OBJECTSCRIPT { NEW SQLCODE,%ROWID,%ROWCOUNT #SQLCOMPILE SELECT=LOGICAL &sql(DELETE FROM PCONT WHERE PSPECNO = :Specno) If $Get(%sqlcontext)'=$$$NULLOREF Set %sqlcontext.%SQLCODE=SQLCODE,%sqlcontext.%ROWCOUNT=%ROWCOUNT Quit:SQLCODE<0 &sql(DELETE FROM PSPEC WHERE PSPECNO = :Specno) If $Get(%sqlcontext)'=$$$NULLOREF Set %sqlcontext.%SQLCODE=SQLCODE,%sqlcontext.%ROWCOUNT=%ROWCOUNT }