ALTER proc [dbo].[p_Func_NewPropertyUnitLoginName]
@loginname nvarchar(max) output
as
declare @prefix nvarchar(20)='cqdw'--前缀
declare @suffixLength int=6--后缀(流水号)长度
declare @zero nvarchar(max)=''--流水号补零
--流水号补零
declare @i int=1
while @i<@suffixLength
begin
set @zero+='0'
set @i+=1
end
set @loginname=(
select @prefix+SUBSTRING(accountnum,len(accountnum)-(@suffixLength-1),@suffixLength)--去掉多余的0,并加前缀
from (
select @zero+convert(nvarchar(10),isnull(MAX(num),0)+1) as accountnum--最大流水号+1
from (
select
convert(int,SUBSTRING(LoginName,3,LEN(LoginName)-2)) as num--所有符合要求的流水号(长度符合要求&前缀相同&除了前缀剩下的部分为数字)
from dbo.Auth_Accounts
where LEN(LoginName)=len(@prefix)+@suffixLength and SUBSTRING(LoginName,1,len(@prefix))=@prefix and ISNUMERIC(SUBSTRING(LoginName,len(@prefix)+1,LEN(LoginName)-len(@prefix)))=1
) a
) b
)
结果为“cqdw000001”开始,流水号每次+1