At 05:23 a.m. 2/11/2013, jim.rhema316@... wrote:

>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)) A S
>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.

SET TERM is a statement for use in isql and scripts that you are going to run in isql. Its purpose is to change the TERMinator character for DDL and DML statements within the script from the standard semicolon to "something else", viz. any short string, and from "something else" back to semicolon. The reason is that PSQL requires the semicolon to terminate statements within SP and trigger definitions.

So, you have this back-to-front. If this were isql, or an isql script, it would have to be:

SET TERM #; -- switches DDL/DML terminator to #

CREATE PROCEDURE FindSalesAmt(Name VARCHAR(20)) RETURNS (price decimal(18,2), id bigint,
description VARCHAR(90)) A S
BEGIN
SELECT startprice, itemid, title from ebayrevisefile
WHERE price > 20.00
INTO :price, :id, :description;
SUSPEND;
END; -- Signals the end of the PSQL module definition. Internal END statements do not have terminators

SET TERM ;# -- DDL/DML terminator reverts to semicolon

The script engines in some tools will recognise and process the SET TERM commands and do something internal to the tool that has the same effect. Others just throw an error.

GRANT EXECUTE ON PROCEDURE FindID to PUBLIC WITH GRANT OPTION;
suspend; -- illegal
end -- illegal

Your subsequent command should work but following SUSPEND and END statements are illegal in DML and DDL. They work ONLY in procedural language modules (SPs, triggers and EXECUTE BLOCK). GRANT EXECUTE is a DDL command and, in fact, *it* would be illegal in a PSQL module.

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

You need to find out the rules for running scripts in your DBVisualizer tool.


Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"
http://www.firebird-books.net
__________________________________________________________________