Written by

Question Louie McLaughlin · Mar 6, 2023

Create Stored procedure with multiple sql statements

#Caché #SQL

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

Discussion (1)0

Add reply

Comments

Vitaliy Serdtsev · Mar 7, 2023

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
}

0 0