I usually solve this by recursive common table expression:

with recursive
CTE_SPLIT as
(select
P,
iif(P > 0, position(';', CMDS, P+1), 0) as P1,
iif(P > 0, left(CMDS, P-1), CMDS) as CMD,
CMDS
from (select
position(';', CMDS) as P, CMDS
from (select 'CMD1;CMD2;' as CMDS from RDB$DATABASE))
union all select
P1 as P,
iif(P1 > 0, position(';', CMDS, P1+1), 0) as P1,
iif(P1 > 0, substring(CMDS from P+1 for P1-P-1), substring(CMDS
from P+1)) as CMD,
CMDS
from CTE_SPLIT
where P > 0)

select CMD from CTE_SPLIT where CMD <> ''

Be aware of recursion depth limitation (1024; you might count CMD on the
fly, stop at 1023 and rerun query/procedure on the remaining CMDs). The
commands are not serparated by new line in this example.

Cheers, Thomas

Am 21.06.2014 08:36, schrieb majstoru@... [firebird-support]:

>
>
> Hello,
>
>
> I have an idea to transfer some client side code to Firebird SP and
> speed up and secure those work.
>
>
> My client side application procedure is collect some SQL statements from
> 1 to n depent of situation and made some unique SQL statement divided by
> ; and execute it from app.
>
>
> Now I need to transfer that logic to Firebird SP, but I have one
> limitation EXECUTE STATEMENT will execute only one SQL statement it not
> support SQL scripts (more SQL statements divided by ; for example)
>
>
> Is there any idea how ti solve this scenario, thanks in advance...
>
>

--
Mit freundlichen Grüßen,

Thomas Beckmann
Diplom-Informatiker


Wielandstraße 14c • 23558 Lübeck
Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604
Mail thomas.beckmann@... <mailto:thomas.beckmann@...>

ASSFINET-Logo

*ASSFINET Dienstleistungs-GmbH*
Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn
info@... <mailto:info@...> • www.assfinet.de
<http://www.assfinet.de/>

Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann
Registergericht Koblenz HRB 23331

Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der
richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Mail.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist
nicht gestattet.