On 30-1-2013 19:41, red_october2009 wrote:

> I have a CPU intensive stored procedure. It only runs occasionally at user request, and when it runs, my server is busy running the stored proc, and other users using the same DB notice a severe delay or they even get a "not responding" message on my Delphi front end.
>
> I have some loops going on in the stored proc where I supposed I could put some "SUSPEND" statements but I just want to be sure about how SUSPEND works. There are two possible scenarios and I need to know which is true:
>
> a) SUSPEND is like a Application.ProcessMessages in Windows API/Delphi. Which means, I can throw in as many as I like in my stored proc and at any place. It will allow a chance for communication between calling client and server. (a.1: and will also not overload the server so much, allowing other clients to continue, perhaps with some delay, but not as bad as without the SUSPEND statements)
>
> b) SUSPEND is like EXIT, in that process flow "jumps out" of the stored proc at the point where it appears, and any lines following the SUSPEND do not get executed
>
> Which statement is true, and is sub-statement a.1 true?
>
> Thanks in advance for any help you can provide.

It is actually none of the above (it is almost a, but not entirely).
SUSPEND signals that the current values of the output variables should
be returned to the client. In essence any stored procedure that contains
SUSPEND is a selectable stored procedure. Each call to SUSPEND produce a
new row (with the current values of the output variables).

Or as the Interbase 6.0 Language Reference (page 177) says:

Suspends execution of a select procedure until the next FETCH is issued
and returns values to the calling application. Available in stored
procedures only.

Syntax
SUSPEND;
Description
The SUSPEND statement:
* Suspends execution of a stored procedure until the application issues
the next FETCH.
* Returns values of output parameters, if any.

A procedure should ensure that all output parameters are assigned values
before a SUSPEND.
SUSPEND should not be used in an executable procedure. Use EXIT instead
to indicate to the reader explicitly that the statement terminates the
procedure.

Mark
--
Mark Rotteveel