存储过程中生成ID

/*******************************************************

模块名称: 

模块功能: 生成数据表的关键字值,以输出参数的形式返回。

参数说明:  @id需生成的关键字段变量。

*******************************************************/

CREATE procedure [dbo].[SP_DB_NEWID] (@id varchar(20) out)
as
begin
 set nocount on
 declare @year integer,
  @month integer,
  @day integer,
  @hour integer,
  @minute integer,
  @second integer,
  @ms integer,
  @now datetime
 declare @ts varchar(30)
 declare @tmpTable table (ts timestamp, i integer)
 
 insert @tmpTable (i) values (1)
 select @ts = convert(varchar(30), convert(bigint, ts)), @now = getdate()
 from @tmpTable where i = 1
 select @year = datepart(year, @now),
  @month = datepart(month, @now),
  @day = datepart(day, @now),
  @hour = datepart(hour, @now),
  @minute = datepart(minute, @now),
  @second = datepart(second, @now),
  @ms = datepart(millisecond, @now)
 
 select @id = right('00' + cast(@year % 100 as varchar(20)), 2) +
   (case
    when @month < 10 then str(@month, 1)
    else char(ascii('A') + @month - 10)
   end) +
   (case
    when @day < 10 then str(@day, 1)
    else char(ascii('A') + @day - 10)
   end) +
   (case
    when @hour < 10 then str(@hour, 1)
    else char(ascii('A') + @hour - 10)
   end) +
   right('00' + cast(@minute as varchar(20)), 2) +
   right('00' + cast(@second as varchar(20)), 2) +
   right('00' + cast(@ms as varchar(20)), 3) +
   right('00000000' + @ts, 8)
end
 

软件人生-------男装、女装这几天有新款上市哦,大家有兴趣进来看看http://icefish.taobao.com/
原文地址:https://www.cnblogs.com/IcefishBingqing/p/1992501.html