Need Help on Store Procedure to generate Custom ID for each Asset

Question

5.00/5 (1 vote)

See more:

SQL-Server-2005

, +

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 6-Jan-12 23:10pm

tonydsouza1987

Updated 6-Jan-12 23:17pm

Mehdi Gholam

v3

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Amir Mahfoozi · Accepted Answer · 2012-01-07T01:18:00

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.