Jump to content
Sign in to follow this  
Henry Olive

Firebird-Create Procedure

Recommended Posts

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 ;

 

Share this post


Link to post

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 ; 

 

Share this post


Link to post

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

  • Like 1

Share this post


Link to post

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

 

Share this post


Link to post

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.

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×