Subject RE: Problems trying to create Stored Procedure
Author
Post date 2013-11-01T17:14:08Z

According to the folks at DBVisualizer the set term is not a recognzed statement.  I have to use the @delimiter command.


Still getting an error:


@delimeter #;

CREATE PROCEDURE FindSalesAmt(Name VARCHAR(20)) RETURNS (price decimal(18,2), id bigint,

 description VARCHAR(90)) AS

BEGIN 

  SELECT startprice, itemid, title from ebayrevisefile

  WHERE price > 20.00

  INTO :price, :id, :description;

  SUSPEND;

END#


@delimeter ;#

GRANT EXECUTE ON PROCEDURE FindSalesAmt to PUBLIC WITH GRANT OPTION;

  suspend;

end


Here is the error:

 13:13:11  [@DELIMETER - 0 row(s), 0.000 secs]  Command processed

 13:13:11  [CREATE - 0 row(s), 0.000 secs]  [Error Code: 335544569, SQL State: HY000]  GDS Exception. 335544569. Dynamic SQL Error

SQL error code = -104

Token unknown - line 8, column 4

#


Thank you again for your help.


Jim



---In firebird-support@yahoogroups.com, <firebird-support@yahoogroups.com> wrote:

Good afternoon.

Thank you for taking the time to help me on this.  I am new to creating and using Stored Procedures so please bear with me.

I am trying to create this stored procedure:

SET TERM #;
CREATE PROCEDURE FindSalesAmt(Name VARCHAR(20)) RETURNS (price decimal(18,2), id bigint,
 description VARCHAR(90)) AS
BEGIN 
  SELECT startprice, itemid, title from ebayrevisefile
  WHERE price > 20.00
  INTO :price, :id, :description;
  SUSPEND#
END#
SET TERM ;#
GRANT EXECUTE ON PROCEDURE FindID to PUBLIC WITH GRANT OPTION;
  suspend;
end

And when I compile it I get an error on the set term.  As I understand it the ; is a terminator so I need to use a different one within the procedure.

Any help on this is appreciated.  I am using Firebird 2.5 with DBVisualizer Pro 9.0.3. 

Thank you!