如何使用while循环增加自定义ID字段

Question

我正在尝试生成一个ID，其中格式为000H-Y，数字从000到019。我的代码只生成从010H-Y到019H-Y的代码。while循环是否存在格式问题或错误？

if exists(select * from sysobjects where name = 'Bikes')
drop table Bikes
go
create table Bikes
(
BikeID nchar(6) not null,
constraint pk_Bikes_BikeID primary key(BikeID),
constraint chk_Bikes_BikeID check(BikeID like ('0[0-19][HYS]-[AP]')),
StableDate date not null
default getdate()
)
go
if exists(select * from sysobjects where name = 'PopulateBikes')
drop procedure PopulateBikes
go
create procedure PopulateBikes
@outcome as nvarchar(12) output
as
declare @min as int = 0, 
@max as int = 19
if @@ERROR <> 0
return -1
while(@min < @max)
begin
set @min+=1
insert into Bikes(BikeID)
values ('0'+ cast(@min as nvarchar(2))+ 'H-A')

end
go
declare @retVal as int = 0
exec @retVal = PopulateBikes 'SPName:OK'
if @retVal = -1
print 'Error encountered!'
go
select * from Bikes go

Answer 1

您可以使用递归CTE

WITH CTE AS
(
SELECT 1 N
UNION ALL
SELECT N + 1
FROM CTE
WHERE N <= 18
)
SELECT CAST(N AS VARCHAR(10)) + 'H-Y'
-- or SELECT '0' + CAST(N AS VARCHAR(10)) + 'H-Y'
FROM CTE;

不需要使用WHILE循环，这会降低性能。

此外，您的CHECK约束是wron，它应该像一样

CONSTRAINT CH_Bikes_BikeID CHECK(BikeID LIKE '0[0-9]H-Y' OR BikeID LIKE '0[0-9][0-9]H-Y')

因此，这是您的表和填充它的查询

CREATE TABLE Bikes
(
BikeID VARCHAR(10) NOT NULL,
StableDate DATE NOT NULL DEFAULT GetDate(),
CONSTRAINT CH_Bikes_BikeID CHECK(BikeID LIKE '0[0-9]H-Y' OR BikeID LIKE '0[0-9][0-9]H-Y'),
CONSTRAINT PK_Bikes_BikeID PRIMARY KEY (BikeID)
);
WITH CTE AS
(
SELECT 1 N
UNION ALL
SELECT N + 1
FROM CTE
WHERE N <= 18
)
INSERT INTO Bikes (BikeID)
SELECT '0' + CAST(N AS VARCHAR(10)) + 'H-Y'
FROM CTE;
SELECT *
FROM Bikes
ORDER BY CASE WHEN LEN(BikeID) = 5
THEN 0
ELSE 1
END;

演示

---

更新

从你提供的创建表的代码来看，我认为你错了，因为我认为这些字符是可能的值，所以你可以将表创建为

CREATE TABLE Bikes
(
BikeID VARCHAR(10) NOT NULL,
StableDate DATE NOT NULL CONSTRAINT DF_Bikes_StableDate DEFAULT GetDate(),
CONSTRAINT PK_Bikes_BikeID PRIMARY KEY (BikeID),
CONSTRAINT CH_Bikes_BikeID 
CHECK(BikeID LIKE '0[0-9][H,Y,S]-[A,P]' OR BikeID LIKE '0[0-9][0-9][H,Y,S]-[A,P]')
);

这里有一个插入数据的例子

WITH CTE AS
(
SELECT 1 N
UNION ALL
SELECT N + 1
FROM CTE
WHERE N <= 18
)
INSERT INTO Bikes (BikeID)
SELECT '0' + CAST(N AS VARCHAR(10)) + 
CASE WHEN N % 3 = 1
THEN 'H-A'
ELSE 'Y-P'
END
FROM CTE
ORDER BY N;
SELECT *
FROM Bikes
ORDER BY CASE WHEN LEN(BikeID) = 5
THEN 0
ELSE 1
END;

CHECK(BikeID LIKE '0[0-9][H,Y,S]-[A,P]' OR BikeID LIKE '0[0-9][0-9][H,Y,S]-[A,P]')行检查可能的值，例如：

• 允许H-A
• 允许Y-P
• 允许S-P
• 不允许K-A
• 不允许AB-AP

Answer 2

解决方案：

考虑如何为BikeID生成值。

使用此行：

select ('0'+ cast(@min as nvarchar(2))+ 'H-Y')

您将生成从01H-Y到019H-Y的值。

试试这个：

select RIGHT('000'+ cast(@min as nvarchar(2)), 3) + 'H-Y'

此外，您还需要将您的支票约束更改为：

constraint chk_Bikes_BikeID check(BikeID LIKE '0[0-9][0-9][HYS]-[AYP]')