Hello Mike,

> 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.

When you use SUSPEND, DB Workbench detects a "select-able" Stored
Procedure and doesn't expect you to change any data inside that stored
procedure. That's why it doesn't signal a "data could be changed do you want
to commit/rollback" and enables the buttons.

In the SQL Editor, you can start a transaction manually, execute the
procedure,
and commit or rollback.

That being said: using SUSPEND in a procedure that is used via SELECT is
not the best thing you can do.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com

>
> 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