Question
· Jun 25, 2020

Execution of the SQL commands block at once

#SQL #Caché

Is there any way to execute many SQL commands at once as an (anonymous)block?

for example in PostgreSQL there is 

DO $$

BEGIN

--here is Inserts/Updates/Deletes

END $$

Or in Oracle  

BEGIN

END; 

Thanks for the answers!

Discussion (3)0
Log in or sign up to continue
DMITRIY SOSNOVIСН · Jun 26, 2020

@Eduard Lebedyuk  Thanks for the answer, but it not what I wanted, but you give me an idea; there is an $SYSTEM.SQL.EXECUTE function, so I add ClassMethod

 ClassMethod EXECUTEBLOCK(aScript As %Library.String(MAXLEN="")) As %Library.String [ SqlName = EXECUTEBLOCK, SqlProc ]
{ 
//for case String value sended from the SQL context like "value"
//e.g.  SQLUser.EXECUTEBLOCK( 'INSERT INTO A(b,c,d)VALUES("d","e","f");INSERT INTO A(b,c,d)VALUES("g","h","h");')
SET replacedScript= $Replace(aScript,$CHAR(34),$CHAR(39))
TRY{    
   SET retval = $SYSTEM.SQL.Execute(replacedScript,2,"MSSQL")
   RETURN retval.%Message
} 
CATCH err {
RETURN err.Name }
}

, which I later called as a regular stored procedure, by 

EXEC(CALL) EXECUTEBLOCK(
'DELETE FROM A WHERE A.Col=1;DELETE FROM A WHERE A.Col=2;DELETE FROM A WHERE A.Col=3;'
)

So it will be three DELETE statements at once.

Anyway, thanks for the idea.

0 0