sql primary key procedure

--------主键生成器---------

--制作人：孔凡吉

--制作日期：2007年8月14日

--------------------------
--判断是否存在这个数据库表
if exists(select name from sysobjects where name='primarykey_table')
drop table primarykey_table
go
--创建表
create table primarykey_table
(
pkID int not null primary key identity(1000,1),
tablename varchar(50) not null ,
producedate smalldatetime not null,
pkNumber int not null,
wordtrack varchar(3) not null
)
go
--判断存储过程是否存在，如果存在删除，如果不存在创建一个新的
if exists (select * from sysobjects where name='makeprimarykey' and type='P')
drop procedure makeprimarykey
go
--创建一个新得存储过程
create procedure makeprimarykey (@primarykey varchar(14) output,@tablename varchar(50))with encryption
as
--定义在存储过程中的变量
declare
@today smalldatetime,
@wordtrack varchar(2),
@producedate smalldatetime,
@year varchar(2),
@month varchar(2),
@pkNumber varchar(6),
@day varchar(2)
--设置当前时间
set @today=getdate()
--看表中是否有记录，如果有就开始把创建时间和当前时间做比较，H?{C;SP>M2eHkR;LuPBif not exists(select tablename from primarykey_table where tablename=@tablename)
begin
insert into primarykey_table(tablename,producedate,pkNumber,wordtrack)
values(@tablename,@today,1,left(@tablename,2))
end
else
begin
select @producedate=producedate from primarykey_table where tablename=@tablename
if(datediff(day,@today,@producedate)<0)
begin
update primarykey_table set pkNumber=1,producedate=@today where tablename=@tablename
end
else if(datediff(day,@today,@producedate)=0)
begin
update primarykey_table set pkNumber=pkNumber+1 where tablename=@tablename
end
else if(datediff(day,@today,@producedate)>0)
begin
raiserror ('时间错误，请调整时间，并与管理员联系',16,1)
return 1
end
end

select @producedate=producedate,@wordtrack=wordtrack,@pkNumber=pkNumber from primarykey_table
where tablename=@tablename
--取出值之后进行赋值以及字符串的拼接

set @wordtrack=left(@tablename,2)
set @pkNumber=case len(@pkNumber)
when 1 then '00000'+rtrim(ltrim(@pkNumber))
when 2 then '0000'+rtrim(ltrim(@pkNumber))
when 3 then '000'+rtrim(ltrim(@pkNumber))
when 4 then '00'+rtrim(ltrim(@pkNumber))
when 5 then '0'+rtrim(ltrim(@pkNumber))
when 6 then rtrim(ltrim(@pkNumber))
end
set @year=right(cast(datepart(year,ltrim(rtrim(@producedate)))as varchar(4)),2)
set @month=case len (datepart(month,@producedate))
when 1 then '0'+cast(datepart(month,ltrim(rtrim(@producedate)))as varchar(2))
when 2 then cast(datepart(month,ltrim(rtrim(@producedate)))as varchar(2))
end
set @day =case len(datepart(day,@producedate))
when 1 then '0'+cast(datepart(day,ltrim(rtrim(@producedate)))as varchar(2))
when 2 then cast(datepart(day,ltrim(rtrim(@producedate)))as varchar(2))
end

set @primarykey = @wordtrack+@year+@month+@day+@pkNumber

go

declare
@primarykey varchar(14),
@ResultRaise int
exec makeprimarykey @primarykey output,'kongguan'
select @primarykey
select @ResultRaise

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10395457/viewspace-964668/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10395457/viewspace-964668/