CREATE procedure SetIndex @Ex char(20), @result varchar(30) output, @Fmt integer as declare @num char(10) SET NOCOUNT on --begin tran update indextable set num=num where 1=2 if not exists(select num from indextable where Ex=@ex ) insert into indextable values(@ex,1) else update indextable set num=num+1 where Ex=@ex -- commit tran select @num=cast(num as char(10)) from indextable where ex=@ex select @num=space(@fmt-len(@num))+@num select @num=replace(@num,' ','0') select @result=rtrim(@ex)+rtrim(@num) SET NOCOUNT offGO
Declare @s varchar(50), @x int Select @s=Convert(VarChar(50),Year(GetDate())_+Convert(VarChar(50),GetDate()))+Convert(Day(VarChar(50),GetDate())) Select @x=Top 1 sID From MyBill Where sID Like @s+'%' order By sID Desc Select @x=SubString(@x,9,100) Select @s=@s+Convert(VarChar(50),@x+1)
这是我刚刚写完的,大家可以看一下吧! CREATE PROCEDURE [dbo].[GetNewCaseNo] @TableName varchar(20), --表名 @FieldName varchar(20), --字段名 @Date datetime, --日期 @iDigit int = 4, --流水号位数 @ErrorMsg varchar(200) output, --返回的错误值 @CaseNo varchar(20) output --返回的单据号 AS /*declare @TableName varchar(20), @FieldName varchar(20), @Date datetime, @iDigit int --流水号位数 set @TableName='indextable' set @FieldName='num' set @Date='2005-10-05' set @iDigit=4 */ if isnull(@TableName,'')='' begin set @ErrorMsg='表名不能为空' return -1 end if isnull(@FieldName,'')='' begin set @ErrorMsg='字段名不能为空' return -1 end if isnull(@Date,'')='' begin set @ErrorMsg='日期不能为空' return -1 end declare @MaxID int, @DateStr varchar(8), @StrNo varchar(20), @OrderNo varchar(20), @TextNo varchar(20), @ZeroDigit int, @Result varchar(20), @SQLStr varchar(200) declare @Year char(4), @Month char(2), @Day char(2) if OBJECT_ID('tempdb..##TempTable') is not null --释放全局临时表资源 drop table ##TempTable set @Year=Convert(char(4),Year(@Date)) set @Month=Convert(char(2),Month(@Date)) if cast(@Month as int)<10 set @Month='0' + @Month set @Day=Convert(char(2),Day(@Date)) if cast(@Day as int)<10 set @Day='0' + @Day set @DateStr=@Year + @Month + @Day set @SQLStr='Select Max(' + @FieldName + ') as MaxID Into ##TempTable From ' + @TableName + ' Where SubString(' + @FieldName + ', 1, 8)='''+ @DateStr + '''' exec(@SQLStr) select @StrNo=MaxID from ##TempTable --取出最大值ID存入变量中 set @TextNo=@StrNo set @OrderNo=right(@StrNo, @iDigit) --取出相应位数的流水号 set @MaxID=cast(isnull(@OrderNo,'0') as int) + 1 set @ZeroDigit=len(@OrderNo) - len(@MaxID) --流水号字符的长度-流水号整数的长度就是产生后应该在流水号整数前加的零的个数 set @StrNo=@MaxID while @ZeroDigit>0 --剩余的前面补零 begin set @StrNo='0' + @StrNo set @ZeroDigit=@ZeroDigit - 1 endif @TextNo is null begin set @ZeroDigit=@iDigit - 1 while @ZeroDigit>0 --如果没有任何值,则流水号从1开始,前面补零 begin set @StrNo='0' + @StrNo set @ZeroDigit=@ZeroDigit - 1 end end set @Result=@DateStr + @StrNo --日期加流水号 return 0 GO
@Ex char(20),
@result varchar(30) output,
@Fmt integer
as
declare @num char(10)
SET NOCOUNT on
--begin tran
update indextable set num=num where 1=2
if not exists(select num from indextable where Ex=@ex )
insert into indextable values(@ex,1)
else
update indextable set num=num+1 where Ex=@ex
-- commit tran
select @num=cast(num as char(10)) from indextable where ex=@ex
select @num=space(@fmt-len(@num))+@num
select @num=replace(@num,' ','0')
select @result=rtrim(@ex)+rtrim(@num)
SET NOCOUNT offGO
Select @s=Convert(VarChar(50),Year(GetDate())_+Convert(VarChar(50),GetDate()))+Convert(Day(VarChar(50),GetDate()))
Select @x=Top 1 sID From MyBill Where sID Like @s+'%' order By sID Desc
Select @x=SubString(@x,9,100)
Select @s=@s+Convert(VarChar(50),@x+1)
CREATE PROCEDURE [dbo].[GetNewCaseNo]
@TableName varchar(20), --表名
@FieldName varchar(20), --字段名
@Date datetime, --日期
@iDigit int = 4, --流水号位数
@ErrorMsg varchar(200) output, --返回的错误值
@CaseNo varchar(20) output --返回的单据号
AS
/*declare @TableName varchar(20), @FieldName varchar(20), @Date datetime, @iDigit int --流水号位数
set @TableName='indextable'
set @FieldName='num'
set @Date='2005-10-05'
set @iDigit=4
*/
if isnull(@TableName,'')=''
begin
set @ErrorMsg='表名不能为空'
return -1
end
if isnull(@FieldName,'')=''
begin
set @ErrorMsg='字段名不能为空'
return -1
end
if isnull(@Date,'')=''
begin
set @ErrorMsg='日期不能为空'
return -1
end
declare @MaxID int, @DateStr varchar(8), @StrNo varchar(20), @OrderNo varchar(20), @TextNo varchar(20),
@ZeroDigit int, @Result varchar(20), @SQLStr varchar(200)
declare @Year char(4), @Month char(2), @Day char(2)
if OBJECT_ID('tempdb..##TempTable') is not null --释放全局临时表资源
drop table ##TempTable
set @Year=Convert(char(4),Year(@Date))
set @Month=Convert(char(2),Month(@Date))
if cast(@Month as int)<10
set @Month='0' + @Month
set @Day=Convert(char(2),Day(@Date))
if cast(@Day as int)<10
set @Day='0' + @Day
set @DateStr=@Year + @Month + @Day
set @SQLStr='Select Max(' + @FieldName + ') as MaxID Into ##TempTable From ' + @TableName
+ ' Where SubString(' + @FieldName + ', 1, 8)='''+ @DateStr + ''''
exec(@SQLStr)
select @StrNo=MaxID from ##TempTable --取出最大值ID存入变量中
set @TextNo=@StrNo
set @OrderNo=right(@StrNo, @iDigit) --取出相应位数的流水号
set @MaxID=cast(isnull(@OrderNo,'0') as int) + 1
set @ZeroDigit=len(@OrderNo) - len(@MaxID) --流水号字符的长度-流水号整数的长度就是产生后应该在流水号整数前加的零的个数
set @StrNo=@MaxID
while @ZeroDigit>0 --剩余的前面补零
begin
set @StrNo='0' + @StrNo
set @ZeroDigit=@ZeroDigit - 1
endif @TextNo is null
begin
set @ZeroDigit=@iDigit - 1
while @ZeroDigit>0 --如果没有任何值,则流水号从1开始,前面补零
begin
set @StrNo='0' + @StrNo
set @ZeroDigit=@ZeroDigit - 1
end
end
set @Result=@DateStr + @StrNo --日期加流水号
return 0
GO