James Goodwin (2/28/2011)
That's a fairly nice solution to the problem, however...(There's always a but...)The biggest problem I see with your approach is that when someone wants to add a new key-value pair they also need to add a new table. I dislike needing to alter the design of the database just because someone wants to add a data value.
Therefore: Wouldn't it be easier to use a single nextnum table instead of one for each key? Your get sequence number code would work the same and you wouldn't need to use dynamic SQL.
That is what they started with but they had locking and blocking issues while updating that one nextnum table to obtain the next key value.
I suspect that they would only add a new key when they add a new table, so instead of just adding one table you add two. Is the solution perfect? No, but it fixed the problem they were encountering without having to do a major redesign of the database or the application.