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 )
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
if(datediff(day,@today,@producedate)<0)
begin
update primarykey_table set pkNumber=1,producedate=@today where
end
else if(datediff(day,@today,@producedate)=0)
begin
update primarykey_table set pkNumber=pkNumber+1 where
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
--取出值之後進行賦值以及字串的拼接

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@]