Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server >> Transact-SQL

How Can I use this code for Primary Key autogeneration

Hi

DECLARE @MyValue varchar(10)
SELECT @MyValue = (SELECT RIGHT(YEAR(GETDATE()),1)+
REPLACE(STR(MONTH(GETDATE()),2),' ','0')+
REPLACE(STR(PlaceID,2),' ','0')+
'00001'
FROM Provinces WHERE PlaceName='Kinshasa' )
SELECT @MyValue

I written a code like this to generate (example: 5080100001 - 5 Year, 08 Month, 01 PlaceID, remaining digit should be automatic increment for the current month) unique number for everymonth. According to my imagination it will increment automatically for each month. Now I want to assign this value to my primary key field. How it's possible in SQL Server 2000.

Thanks in advance
Jose

[663 byte] By [JoseTA] at [2008-2-27]
«« WinForms 2005 - "Ctrl" drag on a form design.
»» SQL Server 2005 Express June CTP Setup Error
# 1

Here is one way.
Note: this contains undocumented/supported trick. Use at your own risk.

create table seed(i int)
insert seed values(0)
go
create proc getval
as
begin
set nocount on
declare @i int
update seed
set @i=i=i+1
select convert(char(4),getdate(),12)+right(1000000+@i,6) as Idea
end
go
create function dbo.pkgen()
returns char(10)
as
begin
return(select i from openquery(sqlserver_name,'exec tempdb..getval;commit')x)
end
go

create table t(pk char(10) primary key default dbo.pkgen(),i int)
go

insert t(i) values(10)
insert t(i) values(20)
insert t(i) values(30)
select * from t
go

oj at 2007-9-9 > top of Msdn Tech,SQL Server,Transact-SQL...
SQL Server
Transact-SQL

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified