我使用VARCHAR列作为我的主键。我想自动递增它(基数62,小写/大写,数字),但是,下面的代码失败了(原因很明显):
CREATE TABLE TESTING
(
ID VARCHAR(10),
NAME VARCHAR(15),
DESCP VARCHAR(50)
);创建用于测试插入具有NVARCHAR自动递增ID的新记录的过程:
CREATE PROCEDURE SP_INSERT
@NAME VARCHAR(MAX), @DESCP VARCHAR(MAX)
AS
BEGIN
/* Logic for getting new ID as per the NAME with PRE FIX */
DECLARE @NEWID VARCHAR(5);
DECLARE @PREFIX VARCHAR(10);
SET @PREFIX = UPPER(SUBSTRING('STR', 1, 3))
SELECT @NEWID = (@PREFIX + replicate('0', 3 - len(CONVERT(VARCHAR, N.OID 1))) + CONVERT(VARCHAR, N.OID + 1))
FROM
(SELECT
CASE
WHEN MAX(T.TID) IS NULL
THEN 0 ELSE MAX(T.TID)
END AS OID
FROM
(SELECT SUBSTRING(ID, 1, 1) AS PRE_FIX,
SUBSTRING(ID, 2, LEN(ID)) AS TID
FROM Testing) AS T
WHERE
T.PRE_FIX = @PREFIX) AS N
/* INSERT QUERY FOR NEW RECORD */
INSERT INTO Testing VALUES (@NEWID, @NAME, @DESCP)结束
我尝试插入一个值:
SP_INSERT 'svce','YOUR MANAGEMENT DESCRIPTION';第一次,再次尝试插入我得到的相同ID是不递增的。
像这样:
ID Name
------------
SVC001 SVCE
SVC001 Svce发布于 2016-05-02 19:25:01
您可以尝试使用COMPUTED COLUMN,如下所示:
create table t
(
Id int auto_increment,
ComputedId as 'SVC' + convert(nvarchar(60), lpad(Id,3,'0')),
Data nvarchar(60)
)发布于 2016-05-02 19:50:44
我写了一个小剧本,
declare @t table (id varchar(10))
insert into @t values ('SVC001'),('SVC20902')
Declare @NEWID int
select @NEWID=isnull(max(replace(id,'SVC',''))+1,1) from @t
select 'SVC'+ replicate('0',CASE WHEN 3-len(@NEWID)<=0
THEN 0 ELSE 3-len(@NEWID) END)+cast(@NEWID as varchar(10))发布于 2016-05-28 14:06:03
最后我得到了Auto_Increment ID的解决方案。我完成了以下解决方案的代码。代码为:
alter PROCEDURE SP2_INSERT
@NAME VARCHAR(MAX),
@DESCP VARCHAR(MAX)
AS
BEGIN
DECLARE @NEWID VARCHAR(10);
DECLARE @PREFIX VARCHAR(MAX);
SET @PREFIX = UPPER(SUBSTRING(@NAME,1, 3))
SELECT @NEWID = (@PREFIX + replicate('0', 3 - len(CONVERT(VARCHAR,N.OID + 1))) + CONVERT(VARCHAR,N.OID + 1)) FROM (
SELECT CASE WHEN MAX(T.TID) IS null then 0 else MAX(T.TID) end as OID FROM (
SELECT SUBSTRING(EmployeeID, 1, 1) as PRE_FIX,SUBSTRING(EmployeeID, 2, LEN(EmployeeID)) as TID FROM EmployeeMasters
) AS T WHERE T.PRE_FIX = @PREFIX
) AS N
INSERT INTO Testing VALUES (@NEWID,@NAME,@DESCP)
endhttps://stackoverflow.com/questions/36980671
复制相似问题