Click here to Skip to main content
15,947,785 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
Dear All,
I need a help on creating a store procedure which generates custom IDs for each asset. I am programming for Fixed Assets in VB6 with SQL Server 2005. For example, when a new Asset is added ,I need to auto generate the ID based on existing IDs. New ID should not exist in tblAssets table.
SQL
Table Name : tblAssets
Fields : AssetID > Int,Primary Key,this is internal ID (identity seed)
         AssetExtID >nvarchar(50),this is external ID, need to generate/user entered.
Below is the example of data in tblAssets :
AssetID   AssetExtID   ProjectID     ItemName   Qty   UOM   UnitCost .....
1         PROSP-00001  PROSPERITY    SPLIT-AC   2     NOS   $200
2         PROSP-00002  PROSPERITY    LAPTOP     1     NOS   $500
3         UNIII-00001  UNION III     LAPTOP     5     NOS   $400
4         UNIII-00002  UNION III     RECEIVER   2     NOS   $312


The AssetExtID depends on the ProjectID which is in tblProjects.
I will take care of the first 5 characters to generate.But the number part I need to generate by checking existing data. The AssetExtID should not be duplicate. Its unique for each asset. Please help on this. Your help is always appreciated.


Thank You..
Posted
Updated 6-Jan-12 23:17pm
v3
Comments
Amir Mahfoozi 7-Jan-12 5:40am    
How should we know that PROSPERITY leads to PROSP and "UNION III" leads to "UNIII" ? What is the logic behind it ?
tonydsouza1987 7-Jan-12 6:19am    
when a user enter a new asset, first he/she need to select "Project" from the list. when they select project, I will take the first 5 characters from the tblProjects table from field ProjectCode. If they dont select project then entry will not be saved.
tblProjects Fields : ProjectID,ProjectName,ProjManager,ContractNo,ProjectCode.

Project code will be PROSP,UNIII,......

Thank your for your concern...

1 solution

As I know you can not use Triggers or Computed fields for AssetExtID but you can update AssetExtId periodically :

SQL
with a
as
(
SELECT [AssetId]
      ,[ProjectID]
      , SUBSTRing(CAST(100000 + ROW_NUMBER() over (partition by projectid order by assetid) as varchar(max)), 2 , 5) rn
  FROM [Test].[dbo].[tblAsset]
), b as
(
  select a.* , p.projectcode from a inner join tblprojects p on a.projectid = p.projectid
)
,c as
(select * , projectcode +'-' + rn as a2 from b
)
update tblasset
set assetextid = a2
from tblasset inner join c on tblasset.assetid = c.assetid


This will work fine until you delete some previously inserted asset from your table.

Consider this case : you have 5 assets in project A then remove the 3rd one and then add a new one. At this point you have 2 assets that have the 5th position in this project assets, and of course they have the same ID of A-00005 !

So if you remove assets after insertion try to have another scheme for generation of your custom fields.

Hope it helps.
 
Share this answer
 
Comments
tonydsouza1987 7-Jan-12 9:29am    
No.. this wont help me I think. Becuase I have the Delete Option and also I delete via code when needed. And there should not be same ID for two assets. Any other custom ID generation scheme? If there is no other option then I will remove the Asset Delete option from my user interface.
Amir Mahfoozi 7-Jan-12 13:45pm    
If AssetExtId is compound of ProjectCode + '-' + AssetId then you have a unique Id and possibility of deleting without any side effect.
tonydsouza1987 8-Jan-12 6:17am    
Thank you Mr Amir for your prompt reply. I modified slightly and got solved my problm...
Amir Mahfoozi 8-Jan-12 7:25am    
You're welcome.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900