I have some questions that are all related and I am hoping someone can help.
Firstly, is it possible to create a sequence generator using parameters in the create statement? I would like to be able pass the sequence name start value increment value and max values as parameters.
Secondly is it possible to customize the sequence generator to use letters as well as numbers.
The reasons for these questions are because the application I am writing uses a sequence generator that I created and I would like to see if I can modify it to use a sequence generator instead of keeping track of these counters in a table.
The scenario is this, the key field in my primary table is a four position text field. The first character is the first letter of a card name, the remainder of the key is a sequence number. The start value is 1 and the max value is 999. The name of the sequence is TableName_Letter. As an example. Card Name starts with A and Table Name is tblCards. Sequence Name is tblCards_A and key looks like A001.
When the counter portion of the sequence maxes out the prefix changes to AA and max value becomes 99. This process continues until the key eventually reaches AZZZ and at that point the sequence has been maxed out.
I see the possibility for this to work using sequences if I can just work out a few details. The first being creating the sequence using parameters The second being keeping track of the rolling prefixes. I'm thinking that can be done in the name of the sequence itself because every time the prefix gets modified it would require dropping the current sequence and creating a new one with new starting value and new max value. For instance the first roll over for a sequence starting with A would be AA it's start value would be 1 and it's max value would be 99 so it's name could be tblCards_AA.
I hope my explanation is clear enough. Any assistance you can provide is appreciated.