Dear SQLers:

I continue slowly putting together a Banking database. I now
want to define the Transactions table. It makes sense to keep
transactions together somehow. For example, a transfer from one
account to another is composed of a transfer out and a transfer in. It
would be good to be able to see all parts of such a batch.

I suppose that transactions could be corrected after the fact, so
I am really only concerned with a batch balancing when it is entered.
How do I generate the batch number? (I do not want the application
doing this. I want it done in a stored procedure.)

If I have a common attribute for each transaction in a batch, I
could do something like:
create table TranBatches
(
BatchNr int identity(1,1),
CommonAttr whoknows
)
This common attribute might be transaction date. The transaction
table would have an FK into TranBatches.

What if there is no common attribute? Then, all I would have is
for TranBatches is a table of numbers. This strikes me as silly.

I suppose that I could roll my own with a table containing the
last batch number generated and add one to it in the stored procedure
I come up with for batch processing, but this strikes me as kludgy.

Is there an elegant way to generate batch numbers for the
transactions table, bearing in mind that a batch number will be used
for more than one row in the transactions table?

Sincerely,

Gene Wirchenko

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
Not really. Or, there may be reason to have some more information anyway.

First of all, IDENTITY may be a good choice here. In finance there is
often a requierment that voucher numbers are contiguous. That is not
possible to achieve with IDENTITY. In fact that's a feature of IDENTITY:
having contiguous numbers means that you cannot easily have concurrent
inserts. IDENTITY is good for a concurrency, because if an insertion
fails or the transaction is later rolled back, the generated identity
number is nevertheless rolled back. Meaning that other processes can
get their IDENTITY values without waiting.

Rolling your own can be done in multiple ways. With a table with a
row, you can do:

BEGIN TRANSACTION

SELECT @nextid = coalesce(max(id), 0) FROM tbl WITH (UPDLOCK)
INSERT tbl(id, ...)
VALUES (@nextid, ...)

-- more stuff
COMMIT TRANSACTION

In the system I work with, we have a few one-column one-row tables that all
they do is to hold the next voucher number in some series.

We also have a table which holds all voucher numbers generated in all
series, and this table also holds information about which procedure that
generated the number, which user and when. Good for auditing. And a good
cover-up if some code mistakenly grabs a number, even if it has no work
to do.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx