September 23, 2005 at 1:10 pm
We have tables with Projects, Departments linked to Projects, Rooms linked to Departments, and Project Equipment linked to Rooms.
We have two identifier fields, which are essentially human readable "part numbers." These values are not related to table primary key fields.
- PRN (Project Room Number) is used to identify entries in the ProjectRoom table.
- PEN (Project Equipment Number) is used to identify entries in the ProjectEquipment table.
These values are both varchars and follow a similar pattern: 3434_0000001
The first portion of the field is a well known project number, followed by an underscore, and then the second number is a zero left-padded sequential number. The sequential numbers are specific to the project number.
Examples:
Project number 3434 could have PEN or PRN values:
3434_0000001
3434_0000002
3434_0000003
3434_0000004
...
3434_0055555
3434_0055556
3434_0055557
Project 5566 could have the following PEN or PRN values with the sequential values restarting at 0000001
5566_0000001
5566_0000002
5566_0000003
5566_0000004
...
5566_0055555
5566_0055556
5566_0055557
I can create this using a UDF with single row inserts. But my sticking point here is creating correct sequential values during multi-row inserts.
A good example is processing a copy of ProjectEquipment from one room into another room. Ideally I would like to be able to accomplish the following:
INSERT INTO ProjectEquipment
(Description, PEN, ...)
SELECT Description, dbo.UDFGeneratePEN(@ProjectNumber), ...
FROM ProjectEquipment
WHERE ProjectRoomDestinationId = @SourceRoomId
Either via a UDF call on the SELECT, a default value setting on the PEN column in the ProjectEquipment table, or as a last resort, by using a trigger.
Thoughts (thx in advance)
September 23, 2005 at 1:37 pm
How about creating a table with 2 columns 1 for project and 1 for SEED.
Yuu would then use (inside a transaction) a RETRIEVE / UPDATE (to increment by 1) with using UPDLOCK (see BOL for more information)
All you would need to do then is concat the 2 together to get your next value
Good Hunting!
AJ Ahrens
webmaster@kritter.net
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply