有传入的级别字符串吗?
如“0001”
declare @top varchar(10)
set @top='0001'
declare @result varchar(20)
select @result=max(GroupID) from tablename where left(GroupID,len(@top)) and len(GroupID)=len(@top)+2set @result=@top+right('0'+cast(cast(left(@result,2) as int)+1 as varchar(2)),2)
如“0001”
declare @top varchar(10)
set @top='0001'
declare @result varchar(20)
select @result=max(GroupID) from tablename where left(GroupID,len(@top)) and len(GroupID)=len(@top)+2set @result=@top+right('0'+cast(cast(left(@result,2) as int)+1 as varchar(2)),2)
set @top='0001'
declare @result varchar(20)
set @result=@top+'00'
select @result=max(GroupID) from tablename where left(GroupID,len(@top)) and len(GroupID)=len(@top)+2set @result=@top+right('0'+cast(cast(right(@result,2) as int)+1 as varchar(2)),2)
begin
if (select count(*) from tablename
where GroupName = :Name) = 0
begin
insert into tablename (GroupID, GroupName)
value (:ID, :Name)
end
commit transaction
end:ID 和 :Name可以从外面传入,例如在Delphi里可以这样
ParamByName('ID').AsString := edGroupID.Text;
ParamByName('Name').AsString := edGroupName.Text;以上只是个例子,你再去摸索吧:)
you are welcome
returns varchar(20)
as
begin
declare @result varchar(20)
set @result=@top+'00'
select @result=max(GroupID) from tablename
where left(GroupID,len(@top))=@top and len(GroupID)=len(@top)+2 set @result=@top+right('0'+cast(cast(right(@result,2) as int)+1 as varchar(2)),2)
return @result
end
gocreate proc InsertGroup
@Name varchar(80),
@Top varchar(10)
as
if not exists (select * from tablename where GroupName=@Name)
begin
insert tablename(GroupId,GroupName) values (dbo.GetNextId(@Top),@Name)
return 0
end
return 1
go
没有测试!!
select top 1 right('000000000000' +cast( (cast(groupID as int)+1) as varchar(6)),6) as NewGroupId from GroupMember
where GroupID like '0001__' order by GroupID desc其中根据级数的层次数量决定上面“0”的个数