订单生成流水号

--生成流水号

--创建测试表
create table test(id varchar(18), --流水号,日期(8位)+时间(4位)+流水号(4位)
name varchar(10) --其他字段
)

go
--创建生成流水号的触发器
create trigger t_insert on test
INSTEAD OF insert
as
declare @id varchar(18),@id1 int,@head varchar(12)
select * into #tb from inserted
set @head=convert(varchar,getdate(),112)+replace(convert(varchar(5),getdate(),108),':','')
select @id=max(id) from test where id like @head+'%'
if @id is null
set @id1=0
else
set @id1=cast(substring(@id,13,4) as int)
update #tb set @id1=@id1+1
,id=@head+right('0000'+cast(@id1 as varchar),4)
insert into test select * from #tb
go


--插入数据,进行测试
insert into test(name)
select 'aa'
union all select 'bb'
union all select 'cc'

--修改系统时间,再插入数据测试一次
insert into test(name)
select 'aa'
union all select 'bb'
union all select 'cc'

--显示测试结果
select * from test


--删除测试环境
drop table test

/*--测试结果
id name 
------------------ ---------- 
2004022720430001 aa
2004022720430002 bb
2004022720430003 cc
2004022720430004 aa
2004022720430005 bb
2004022720430006 cc

（所影响的行数为 6 行）
--*/

本文固定链接:http://www.uol123.com/2011/11/06/sql%e7%94%9f%e6%88%90%e6%b5%81%e6%b0%b4%e5%8f%b7.html | 数据库之家

CREATE TABLE [dbo].[Order_OrderNumber](
[OrderNumber] [varchar](20) not NULL unique, 
[OrderHead] [varchar](10) not NULL
)


go


create proc [dbo].[OrderNumber_Proc] (


@name varchar(2)
)
as 


declare @head varchar(12),@id int,@orderNumber varchar(20) 


set @head=convert(varchar,getdate(),112)+replace(convert(varchar(5),getdate(),108),':','')
select @orderNumber=max(OrderNumber) from Order_OrderNumber where OrderNumber like @head+'%'
if @orderNumber is null
set @id=0
else
set @id=cast(substring(@orderNumber,13,4) as int)


begin
set @id=@id+1
set @orderNumber=@head+right('0000'+cast(@id as varchar),4)
end


insert into Order_OrderNumber(OrderNumber,OrderHead) values(@orderNumber, @name)




SELECT @orderNumber