日期:2014-05-16 浏览次数:20511 次
CREATE FUNCTION [dbo].[f_NextBH]()
RETURNS char(8)
AS
BEGIN
RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX([申请编号]),6),0),6) FROM [table] WITH(XLOCK,PAGLOCK))
END
drop table tb
create table tb (申请编号 varchar(20))
go
Alter FUNCTION [dbo].[f_NextBH]()
RETURNS char(13)
AS
BEGIN
RETURN(SELECT 'ABC'+ substring (Cast(year(getdate())as varchar(4)),3,2) + substring(Convert(Varchar(10), GetDate(), 120),6,2)+RIGHT(100001+ISNULL(RIGHT(MAX([申请编号]),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END
go
select * from tb
go
select [dbo].[f_NextBH]()
create proc dbo.pro_getNewBillno
as
begin
set nocount on
declare @h int, @ybillNO varchar(50)
select @h=max(cast(right(ybillNO,4) as int))+1 from dbo.t_salesHeader (nolock)
where ybillNO like 'MS'+substring(convert(varchar,getdate(),112),3,8)+'%'
select @ybillNO='MS'+substring(convert(varchar,getdate(),112),3,8)
+case when @h is null then '0001' else replicate('0',4-len(@h))+rtrim(@h) end
select @ybillNO 'ybillNO'
end
go
create table test(编号 nvarchar(20))
DECLARE @Sequence varchar(5)
SET @Sequence = RIGHT(
(
SELECT MAX(编号)
FROM test
WHERE 编号 LIKE 'ABC'+ replace(substring(convert(varchar(100),GETDATE(),121),3,5),'-','') + '%'
),
5
) &nb