如何让序号随年度变化而调整 找库存商品进销存
2楼: 我想编一个文件收发的小程序,收文序号按年度递增,如是05年度第一次收文,序号是1以后每增加一条依次递增.到06年第一次收文,序号又从1开始递增.如何实现？100分请教. 如库存商品进销存
3楼: 看看这个存储过程对你有没有用处...
--=============================================
--过程用于为新插入的记录生成关键字
--参数说明: @TabName 要插入的表名
-- @FieName 要生成关键字的字段名
-- @PreCode 默认的前缀
--eg:exec NewTableDataNO ＇＇orderm＇＇,＇＇single_no＇＇,＇＇DD＇＇
--=============================================
CREATE PROCEDURE [NewTableDataNO]
@TableName varchar(50),
@FieldName varchar(50),
@PreCode varchar(2)
AS
Declare @TmpPreCode varchar(100),@Sql varchar(1000), @NewNo varchar(50), @I varchar(50)
if not exists(select * from tempdb.dbo.sysobjects where Name = ＇＇##TmpNewNo＇＇)
Create table ##TmpNewNo(RandF float, NewNo varchar(50))
Set @TmpPreCode = @PreCode
--找当天的最大编号
select @TmpPreCode = @TmpPreCode + CONVERT(char(6), Getdate(), 12), @I = cast(RAND() as varchar(50))
set @sql = ＇＇insert ##TmpNewNo(RandF, NewNo)
select ＇＇ + @I + ＇＇, isnull(max(＇＇ + @FieldName + ＇＇), ＇＇＇＇＇＇＇＇)＇＇
+ ＇＇ from ＇＇ + @TableName
+ ＇＇ Where left(＇＇ + @FieldName + ＇＇, len(＇＇＇＇＇＇ + @TmpPreCode + ＇＇＇＇＇＇)) = ＇＇＇＇＇＇ + @TmpPreCode + ＇＇＇＇＇＇＇＇
exec(@Sql)
select @NewNo = NewNo from ##TmpNewNo where RandF = @I
delete from ##TmpNewNo where RandF = @I
--生成新的编号
if @NewNo = ＇＇＇＇ set @I = ＇＇000＇＇ else Set @I = right(@NewNo, 3)
set @I = cast(cast(@I as int) + 1 as varchar(3))
set @I = case len(@I) when 0 then ＇＇001＇＇ when 1 then ＇＇00＇＇ + @I when 2 then ＇＇0＇＇ + @I else @I end
select @TmpPreCode + @I
GO
4楼: 我还没有写过存储过程呢。[:(]
5楼: 顶一下
6楼: SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER proc sp_sale_NewOrderNO
@Res varchar(20) out
as
declare @OrdNO varchar(20),
@Year varchar(10),
@Month varchar(10),
@Day varchar(10),
@Now datetime,
@Num varchar(3)
set @Now = GetDate()
set @Year = right(Year(@Now), 2)
set @Month = right(Month(@Now), 2)
if len(@Month) = 1
set @Month = ＇＇0＇＇ + @Month
set @Day = right(Day(@Now), 2)
if len(@Day) = 1
set @Day = ＇＇0＇＇ + @Day
set @OrdNo = @Year + @Month + @Day
select @Num = IsNull(max(cast(SubString(OrdNo,8,3) as int)), 0) + 1
from t_Sale_ClientOrderMaster where OrdNo like @OrdNo + ＇＇%＇＇
if len(@Num)=1
set @Num = ＇＇00＇＇+ @Num
if len(@Num)=2
set @Num = ＇＇0＇＇+ @Num
--print @OrdNo + ＇＇-＇＇ + @Num
set @Res = @OrdNo + ＇＇-＇＇ + @Num
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
这样的存储过程可以生成类似于060207-001的订单号
联系我们
