At 10:14 PM 27-08-02 +1000, you wrote:

>you should always have the suspend; in there
>even SQL Explorer does not like the absence of suspend

You need suspend only if you want to return a multi-row dataset, i.e. your
stored procedure outputs potentially multiple rows. The purpose of SUSPEND
is literally to suspend processing whilst the current output row from a
cursor operation is passed to the buffer.

In the case of a SP which is executed and returns a set of output
parameters, it is expected that your client will read the output parameters
returned from an EXECUTE PROCEDURE statement in the data parameter buffer,
not try to use the dataset buffer to acquire the result as a singleton select.

The advice to always use SUSPEND on queries that return output is therefore
a bit misplaced. In an EXECUTE procedure, SUSPEND means exactly the same
thing as EXIT.

AFAIK, SQL Explorer doesn't support reading return parameters.

>the unexpected end of command is the END statement since there is nothing
>telling the parse to actually return or what to return

CREATE PROCEDURE SET_TIMEOUT
AS
begin
update online_ set timeout = current_timestamp;
end

If this was the stored procedure, submitted interactively, then the parser
of your client program can not dealing properly with the SET TERM
statements in order to present a complete CREATE PROCEDURE statement. A SP
is a set of statements within a statement, that begins with CREATE
PROCEDURE and ends with the END ^ statement on the outermost BEGIN/END
block (or whatever terminator you set with SET TERM). You need the
alternative terminator character because, within the CREATE PROCEDURE
statement, the PSQL statements must be terminated with semicolons. As it
is, it appears that the compiler reaches the semicolon and barfs because
the terminator wasn't set to tell it that CREATE PROCEDURE statement would
be terminated by ^.

I think you also reported an Unknown Token error when submitting the
statement after setting the terminator. That will be the END statement
floating about with no terminator.

In a script or in ISQL, you would present the CREATE PROCEDURE statement
like this:

SET TERM ^; /* changes the terminator for the next statement to '^') */
CREATE PROCEDURE SET_TIMEOUT
AS
begin
/* statement within a statement, uses the semicolon terminator as required
by the compiler */
update online_ set timeout = current_timestamp;
end ^ /* terminator of the CREATE PROCEDURE statement */

SET TERM ; ^ /* resets the terminator to semicolon */

So, the question remaining is: were you trying to submit the statement
interactively or as a script?
-- If interactively, then the parser of your query tool might be broken
(it's failing to perform a SET TERM); or it is expecting you to perform a
SET TERM yourself before submitting the CREATE PROCEDURE statement.
-- If you were using Script Executive, then you just have some omissions to
rectify.

heLen