On 11/30/2010 10:59 AM, Elkins Villalona wrote:

> I'm using the table approach, and it works pretty well for me. I got a
> stored procedure like the one below to retrieve the next id number.
>

Your procedure will be fine for a single user system, or for
an application where only one transaction calls the procedure.
In a heavily concurrent environment, you'll get large numbers
of Deadlock / Update Conflict errors on the select with lock
that will require you to rollback and retry the transaction.

Good luck,

Ann

>
>
> create procedure SP_GET_ID (PID_NAME varchar(40))
> returns (RID integer)
> as
> BEGIN
> SELECT ID_SEQUENCE + 1
> FROM ID_TABLE
> WHERE ID_NAME =:PID_NAME
> WITH LOCK
> INTO :RID;
>
> UPDATE ID_TABLE
> SET ID_NAME = :RID
> WHERE ID_NAME =:PID_NAME;
>
> SUSPEND;
> END
>
>
>