凌建 回复 wd210010(提问者)ALTER proc [dbo].[create_jnum]
@ShopNum as varchar(20)
as
declare @NUM as int;
declare @ShopNum1 as varchar(10);
set nocount on
begin
if @ShopNum=\'总部\'
begin
select @Num=4-LEN(cast(max(cast(right(jnum,4) as int))+1 as varchar)) from Personnel where shopnum=@ShopNum and jnum like \'U%\';
if @NUM=3
select \'U000\'+cast(max(cast(substring(jnum,2,5) as int))+1 as varchar) Jnum
from Personnel
where jnum like \'U%\'
if @NUM=2
begin
select \'U00\'+cast(max(cast(substring(jnum,2,5) as int))+1 as varchar) Jnum
from Personnel
where jnum like \'U%\'
end
if @NUM=1
begin
select \'U0\'+cast(max(cast(substring(jnum,2,5) as int))+1 as varchar) Jnum
from Personnel
where jnum like \'U%\'
end
if @NUM=0
begin
select \'U\'+cast(max(cast(substring(jnum,2,5) as int))+1 as varchar) Jnum
from Personnel
where jnum like \'U%\'
end
end
else if substring(@ShopNum,1,1)=\'S\'
if @ShopNum=\'S32\' begin set @ShopNum1=\'S32_1\' END
else begin set @ShopNum1=@ShopNum end
begin
print @shopnum1
if not exists (select 1 from personnel where jnum is not null and shopnum=@shopnum)
begin
insert into personnel(post,name,jnum,shopnum,entry_time,state) values(\'店长\',\'默认店长\',@shopnum1+\'0000\',@shopnum,convert(varchar(10),getdate(),23),\'默认\')
end
select @Num=4-LEN(cast(max(cast(right(jnum,4) as int))+1 as varchar)) from Personnel where shopnum=@ShopNum;
if @NUM=3
begin
select @ShopNum1+\'000\'+cast(max(cast(right(jnum,4) as int))+1 as varchar) Jnum from Personnel where shopnum=@ShopNum
end
if @NUM=2
begin
select @ShopNum1+\'00\'+cast(max(cast(right(jnum,4) as int))+1 as varchar) Jnum from Personnel where shopnum=@ShopNum
end
if @NUM=1
begin
select @ShopNum1+\'0\'+cast(max(cast(right(jnum,4) as int))+1 as varchar) Jnum from Personnel where shopnum=@ShopNum
end
if @NUM=0
begin
select @ShopNum1+cast(max(cast(right(jnum,4) as int))+1 as varchar) Jnum from Personnel where shopnum=@ShopNum
end
end
end
1min目标场景问卷
立即参与
