I am migrating an Oracle database to SQL2000, and I've found the following problem:
In Oracle, we use sequences for unique identifiers.
Now, in SQL2000, I have to emulate this behaviour.
It seems an easy problem, but the following problems make it harder:
1. The application using this database cannot receive out parameters from stored procedures. It can execute selects, and receive the result set.
2. The ID of the newly inserted row is required later.
3. Each transaction is either a query or an execution block. (The second has no output.) Make it clear: a fetch and an insert couldn't be in the same transaction.
It is simple in Oracle: first I fetch the ID from the sequence, then I pass it to the stored procedure, which inserts the row, as a parameter.
Because of the restrictions, using identifier fields doesn't work (I cannot retrieve the new ID). The functions IDENT_* have looked good, but they doesn't step the id field.
Has anyone any idea how could I solve this problem?