110,545
社区成员
发帖
与我相关
我的任务
分享
///id是序号
///length是长度
public string GetID(int id,int length)
{
string strID =id.ToString();
strID=strID.PadLeft(length, '0');
return strID;
}
public string GetCode(int codeID)
{
string returnStr = "";
returnStr = "000000" + codeID.ToString();
returnStr = returnStr.Substring((returnStr.Length - 7), (returnStr.Length - 1));
return returnStr;
}
set @currentstring=convert(nvarchar(10),@currentno)
while(len(@currentstring) <@NoLength)
set @currentstring='0'+@currentstring;
select @BillName+@currentstring as CurrentBillNo
go
exec GetBillNo 'ZKS-'
/*创建表BillNo*/
if exists (select * from sysobjects where name='BillNo')
drop table BillNo
go
CREATE TABLE [dbo].[BillNo](
[BillName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,/*前缀字符串,如'ZKS-',*/
[CurrentNo] [int] NOT NULL,/*当前序号*/
[Length] [int] NOT NULL,/*后缀序号长度*/
CONSTRAINT [PK_BillNo] PRIMARY KEY CLUSTERED
(
[BillName] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
go
/*插入*/
insert into BillNo(BillName,CurrentNo,Length) values ('ZKS-',0,6)
go
/*创建存储过程*/
if exists (select * from sysobjects where name='GetBillNo' and type='p')
drop procedure GetBillNo
go
create procedure GetBillNo
@BillName nvarchar(50)/*前缀字符串,如'ZKS-',这要和BillNo表中的BillName相对应*/
as
declare @currentno int
declare @currentstring nvarchar(10)
declare @NoLength int
/*获得当前号码*/
select @currentno=CurrentNo,@NoLength=Length from BillNo where BillName=@BillName
/*如果BillNo表存在前缀为@BillName*/
if(@currentno is not null)
begin
set @currentno=@currentno+1
update BillNo set CurrentNo=@currentno where BillName=@BillName
end
/*BillNo表不存在前缀为@BillName,永远返回0*/
else set @currentno=0
set @currentstring=convert(nvarchar(10[code=C#]
),@currentno)create TABLE aaa
(
codeID Int IDENTITY(1,1),
value int
primary key(codeID)
)
go
insert into aaa (value) values(1)
insert into aaa (value) values(2)
insert into aaa (value) values(3)
insert into aaa (value) values(4)
go
select 'ZKS-' + right('000000'+ Convert(varchar(1000), codeID ),6) youCode from aaa
go
drop table aaa
go
create TABLE test
(
codeID Int IDENTITY(1,1),
value int
primary key(codeID)
)
insert into test (value) values(1)
insert into test (value) values(2)
insert into test (value) values(3)
insert into test (value) values(4)
select 'ZKS-' + right('000000'+ Convert(varchar(1000), codeID ),6) youCode from test
drop table test
create TABLE test
(
codeID Int IDENTITY(1,1)
primary key(codeID)
)
select 'ZKS-' + right('000000'+ Convert(varchar(1000), codeID ),6) youCode from test
drop table test