有订单表SO_Table，单号字段RefNo VARCHAR(10)，需要实现自动编号，格式为YYYYMMXXXX，其中XXXX为序号，如：2004050001，2004050002……2004059999等，采用Transact-SQL实现新订单编号的思

USE [test]
GO
/****** 对象:  Table [dbo].[test]    脚本日期: 01/04/2011 13:21:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[test](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [name] [nchar](50) NOT NULL,
 [ename] [nchar](50) NOT NULL,
 [NewId] [varchar](50) NULL,
 CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
 [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

---

 

 

if exists(select * from sysobjects where name='P_Order' and type='P' )
drop proc P_order
go
create proc P_Order
as
 begin
  declare @MaxId int,@Date varchar(10),@NewId varchar(20)
  select @MaxId=Id from test
  if @MaxId=null
  begin
   set @MaxId=0
  end
  else
  begin
   select @Date=CONVERT(VARCHAR(10),GETDATE(),112)
   set @NewId=@Date+ right(cast(power(10,4) as varchar)+@MaxId,4)
   insert into dbo.test([Name],EName,[NewId]) values('lee','lfs',@NewId)
  end
 end
exec P_Order