Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hamble18
Starting Member
12 Posts |
 Posted - 2009-04-09 : 06:58:44
|
Hello... can anyone tell me if it is possible to do this.I have a sproc that is usually used for inserting new rows into a table. As part of that process it generates a string to be used as a kind of unique product code, which consists of a prefix, then a sequential number, plus a random letter.The sequential number and the prefix are looked up from a table called 'system', and the sproc goes something like this: DECLARE @PC varchar(10) DECLARE @RandChar char(1) UPDATE System SET NextBadgeSequence=NextBadgeSequence+1 SET @RandChar = CHAR(ABS(checksum(newid()))%24 + 65 ) IF @RandChar = 'I' SET @RandChar = 'Y' IF @RandChar = 'O' SET @RandChar = 'Z' SELECT TOP 1 @PC=Prefix+LTRIM(STR(NextSequence))+@RandChar FROM System INSERT INTO Products (ProductCode, .....) VALUES (@PC, ....) There is also an ID field; this isn't my primary key.However, I need to allow bulk insert of rows through other sources. If the Product code isn't provided, I need to generate it along the same lines. So I either need to use a default value or a trigger that will fill this in, but it has to calculate the sequence number and suffix character individually for each row.My understanding is that I can't use a UDF function for this. Can it be done?Many thanks |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
hamble18
Starting Member
12 Posts |
Posted - 2009-04-09 : 09:19:13
|
| Hej PesoThanks for the link. An interesting article, but unfortunately I don't think any of the proposed solutions will work for my specific requirements. Firstly, data is often merged from alternative sources where this code field does not follow the pattern (though will still be a unique string). Secondly, there are other 'business logic' reasons why we need to get the sequential number as a lookup from another table. For example, we need the control to specify and reset the sequence number when necessary, and ensure it is distinct from other systems also generating product codes.Is there any way to write a trigger that will allow a multi-row insert to all have different values generated in this way? |
 |
|
|
|
|
|
|
|
Trigger:Insert calculated data, different each row