在存储过程中需要帮助以为每个资产生成自定义ID [英] Need Help on Store Procedure to generate Custom ID for each Asset

问题描述

亲爱的所有人，
我需要创建存储过程的帮助，该存储过程会为每个资产生成自定义ID.我正在使用SQL Server 2005在VB6中为固定资产编程.例如，添加新资产时，我需要根据现有ID自动生成ID.新的ID不应该存在于tblAssets表中.

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.

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

AssetExtID取决于tblProjects中的ProjectID.
我会照顾要生成的前5个字符，但是我需要通过检查现有数据来生成数字部分. AssetExtID不应重复.它对于每种资产都是唯一的.请帮忙.您的帮助将一直受到赞赏.


谢谢..

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..

推荐答案

200 2 PROSP-00002繁荣膝上型计算机 1 NOS

200 2 PROSP-00002 PROSPERITY LAPTOP 1 NOS

500 3 UNIII-00001 UNION III笔记本电脑 5 NOS

500 3 UNIII-00001 UNION III LAPTOP 5 NOS

400 4 UNIII-00002 UNION III接收器 2 NOS

400 4 UNIII-00002 UNION III RECEIVER 2 NOS

这篇关于在存储过程中需要帮助以为每个资产生成自定义ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！