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]