Firebird-Create Procedure

[Henry Olive 5]

I wish everyone a healthy day.

I'm trying to move from Interbase to Firebird

I cant create below IB Procedure in Firebird ( I made copy in IB-IBConsole and Paste in FB-FlameRobin )

(The proc works well in IB)

I'm getting,    SQL error code = -104  Unexpected end of command - line 26, column 3 (*Which is SUSPEND*)

What is wrong ?
Thank You

CREATE PROCEDURE "CUSTITEMMOVEMENT" 
(
  "CUSTNO" INTEGER
)
RETURNS
(
  "RNO" INTEGER,
  "TNAME" VARCHAR(5),
  "TDATE" DATE,
  "DOCNO" VARCHAR(12),
  "QTY" NUMERIC(18, 2),
  "NETPRICE" NUMERIC(18, 4),
  "ITEMNO" VARCHAR(20),
  "ITEMNAME" VARCHAR(40)
)
AS
 BEGIN
    FOR SELECT  IM.RNO,IM.TNAME,IM.TDATE,IM.DOCNO,IM.QTY,
    IM.NETPRICE,IM.ITEMNO, IT.ITEMNAME
    FROM ITEMMOVEMENTS IM
    JOIN ITEMS IT ON IT.ITEMNO=IM.ITEMNO
    WHERE (IM.CUSTNO= :"CUSTNO")
    ORDER BY IM.TDATE, IM.DOCNO
    INTO :"RNO",:"TNAME",:"TDATE",:"DOCNO",:"QTY",:"NETPRICE",:"ITEMNO",:"ITEMNAME"
    DO
  SUSPEND;  /* Line 26 is here */
END ;

 

[Ruslan 5]

Try to add BEGIN END for SUSPEND

CREATE PROCEDURE "CUSTITEMMOVEMENT" 
(
  "CUSTNO" INTEGER
)
RETURNS
(
  "RNO" INTEGER,
  "TNAME" VARCHAR(5),
  "TDATE" DATE,
  "DOCNO" VARCHAR(12),
  "QTY" NUMERIC(18, 2),
  "NETPRICE" NUMERIC(18, 4),
  "ITEMNO" VARCHAR(20),
  "ITEMNAME" VARCHAR(40)
)
AS
 BEGIN
    FOR SELECT  IM.RNO,IM.TNAME,IM.TDATE,IM.DOCNO,IM.QTY,
    IM.NETPRICE,IM.ITEMNO, IT.ITEMNAME
    FROM ITEMMOVEMENTS IM
    JOIN ITEMS IT ON IT.ITEMNO=IM.ITEMNO
    WHERE (IM.CUSTNO= :"CUSTNO")
    ORDER BY IM.TDATE, IM.DOCNO
    INTO :"RNO",:"TNAME",:"TDATE",:"DOCNO",:"QTY",:"NETPRICE",:"ITEMNO",:"ITEMNAME"
    DO BEGIN
      SUSPEND;  /* Line 26 is here */
    END  
END ; 

 

[Serge_G 87]

Use SET TERM

 

 

SET TERM $ ;
CREATE PROCEDURE CUSTITEMMOVEMENT 
(
  CUSTNO INTEGER
)
RETURNS
(
  RNO INTEGER,
  TNAME VARCHAR(5),
  TDATE DATE,
  DOCNO VARCHAR(12),
  QTY NUMERIC(18, 2),
  NETPRICE NUMERIC(18, 4),
  ITEMNO VARCHAR(20),
  ITEMNAME VARCHAR(40)
)
AS
 BEGIN
    FOR SELECT  IM.RNO,IM.TNAME,IM.TDATE,IM.DOCNO,IM.QTY,
    IM.NETPRICE,IM.ITEMNO, IT.ITEMNAME
    FROM ITEMMOVEMENTS IM
    JOIN ITEMS IT ON IT.ITEMNO=IM.ITEMNO
    WHERE (IM.CUSTNO= :CUSTNO)
    ORDER BY IM.TDATE, IM.DOCNO
    INTO :RNO,:TNAME,:TDATE,:DOCNO,:QTY,:NETPRICE,:ITEMNO,:ITEMNAME
    DO SUSPEND;  
END $
SET TERM ; $

By the way, don't use  quotation marks except if you want columns names to be case-sensitive

and, for purpose of maintenance I suggest you not to name your columns xxxNO if type is not a number

[Henry Olive 5]

Thank you so much Ruslan, Serge
Ruslan, with your suggestion i got same error.

Serge, Your suggestion is OK now i can create SP.

again Thank you

 

[Serge_G 87]

By the way, don't forget to indicate the GUI (or other) you use for the SQL.

With FlameRobin (I guessed) you need these SET TERM, but with IBExpert it wouldn't have been necessary.