用存储过程生成记录编号

 

CREATE   procedure  up_GetBeautifulOrderNumber( @orderNumber   char ( 12 ) out)
as
begin
declare   @number   char ( 11 ) -- 编号的后11位
declare   @maxDate   decimal -- 从数据库表中当前最大的编号中取出的日期
declare   @currentDate   decimal      -- 当前日期
declare   @currentNumber   char ( 12 )  -- 数据库表中当前最大的编号
declare   @day   char ( 2 )  -- 当前日期，如果是一位数，需要将其转换为两位数
set   @day = ( select   case   len ( datename (d, getdate ())) 
  when   1   then   ' 0 ' +   cast ( datename (d, getdate ())  as   char ( 1 ))
  when   2   then   cast ( datename (d, getdate ())  as   char ( 2 ))  end )
set   @currentDate = cast (( datename (yyyy, getdate ()) + datename (mm, getdate ()) +   @day )  as   decimal )
if   not   exists ( select   top   1  OrderID  from  Orders  order   by  OrderID  desc )
begin
  set   @number = cast (( @currentDate   * 1000 )  as   char ( 11 ))
end
else  
begin
   set   @currentNumber = ( select   top   1  OrderID  from  Orders  order   by  OrderID  desc )
   set   @maxDate = cast ( substring ( @currentNumber ,  2 , 8 )  as   decimal )
   if   @maxDate < @currentDate  
     begin
  set   @number = cast ( @currentDate   * 1000   as   char ( 11 ))
     end
   else   if   @maxDate = @currentDate
     begin
  set   @number = cast (( cast ( substring ( @currentNumber , 2 , 12 )  as   decimal ) + 1 )  as   char ( 11 ))
     end
   else  
     begin
  print   @maxDate
  declare   @errMessage   varchar ( 100 )
  set   @errMessage = cast ( @maxDate   as   char ( 10 )) +   ' 错误，数据库表中纪录的最大日期有错误，请与数据库管理员联系。 '
  raiserror ( @errMessage ,  16 ,  1 ) 
     end
end
    set   @orderNumber = ' B ' + @number
end  

转载于:https://www.cnblogs.com/jiangshaofen/archive/2007/04/13/711772.html