What are you using the "suspend" for?

It's normally only used in conjunction with SELECT's in procedures which
return tables.


_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of SoftTech
Sent: Mittwoch, 17. Dezember 2008 19:29
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Why can I not commit this stored procedure?
(Using Database Workbench for testing)



Greetings All,

I have an issue where I can run this stored procedure directly in
DBWorkbench or run it using a SQL statement in DBWorkbench that calls the
stored procedure and sometimes the commit and rollback buttons are enabled
so I can commit the transaction, but most of the time these buttons are not
enabled even though values were returned.

I run this SQL in DBWorkbench which is suppose to keep the 30 most current
records and delete the rest:

SELECT ACCT_ID,
RECORD_COUNT,
RECORDS_DELETED
FROM SPS_PURGE_DIALER_RESULTS(:V_ACCT_ID)

It returns these values:
ACCT_ID 1037
RECORD_COUNT 62
RECORDS_DELETED 32

This is the stored procedure:

SET TERM ^^ ;
CREATE PROCEDURE SPS_PURGE_DIALER_RESULTS (
V_ACCT_ID Integer)
returns (
ACCT_ID Integer,
RECORD_COUNT Integer,
RECORDS_DELETED Integer)
AS
DECLARE VARIABLE iCompanyID SmallInt;
DECLARE VARIABLE iAcctID Integer;
DECLARE VARIABLE iPersonID Integer;
DECLARE VARIABLE iRecCount Integer;
DECLARE VARIABLE iDialerResultID Integer;
DECLARE VARIABLE iProcessCount Integer;
begin
RECORDS_DELETED = 0;

/* Process the account records */
FOR SELECT DISTINCT DR.ACCT_ID
FROM DIALER_RESULTS DR
WHERE DR.ACCT_ID = :V_ACCT_ID
INTO iAcctID DO
BEGIN
SElECT COUNT(*)
FROM DIALER_RESULTS DR
WHERE DR.ACCT_ID = :iAcctID
INTO :iRecCount;

ACCT_ID = :iAcctID;
RECORD_COUNT = :iRecCount;

IF (IRecCount > 30) THEN
BEGIN
FOR SELECT C.COMPANY_ID
FROM COMPANY C
INTO :iCompanyID do
BEGIN
SElECT COUNT(*)
FROM DIALER_RESULTS DR
WHERE DR.ACCT_ID = :iAcctID
AND DR.COMPANY_ID = :iCompanyID
INTO :iRecCount;

IF (IRecCount > 30) THEN
BEGIN
iProcessCount = 0;

FOR SELECT DR.DIALER_RESULTS_ID
FROM DIALER_RESULTS DR
WHERE DR.ACCT_ID = :iAcctID
AND DR.COMPANY_ID = :iCompanyID
ORDER BY DR.CREATE_DATE DESC
INTO :iDialerResultID DO
BEGIN
iProcessCount = iProcessCount + 1;
IF (iProcessCount > 30) THEN
BEGIN
RECORDS_DELETED = RECORDS_DELETED + 1;
DELETE FROM DIALER_RESULTS DR WHERE DR.DIALER_RESULTS_ID = :iDialerResultID;
END
END
END
END
END

SUSPEND;
END
end
^^
SET TERM ; ^^

But I cannot commit. Any ideas?

Thanks,
Mike

[Non-text portions of this message have been removed]







[Non-text portions of this message have been removed]