0

I want my ID to be autogenerated and want a format like this: 

12-0001, 12-0002

12 represent the year 2012 which is the current year of the system date.

And my last record for ID is 12-0999 and when 2013 comes I want my ID to change the year prefix and reset the 4 digit like this:

13-0001, 13-0002.

I'm using asp.net mvc 3 and sql server express 2008.

Can anyone tell me of a way I can do this.

Darin Dimitrov
  • 1,023,142
  • 271
  • 3,287
  • 2,928
keizune
  • 37
  • 3
  • 4
  • @marc_s I don't have a DATE or DATETIME column in my Table. My Table name has a column of EMPLOYEEID which is autogenerated, FIRSTNAME, LASTNAME, BIRTHDATE, ADDRESS, etc. – keizune Jan 23 '12 at 06:25
  • @keizune: then see my response - look at option no. 2 – marc_s Jan 23 '12 at 06:26

1 Answers1

2

I see two options:

(1) if your table that you're inserting data into has a DATE or DATETIME column that has the "right" year in it, you could simply add a persisted, computed column to your table - something like:

ALTER TABLE dbo.YourTable
ADD PKID AS RIGHT(CAST(YEAR(DateColumn) AS CHAR(4)), 2) + '-' + 
                RIGHT('00000' + CAST(ID AS VARCHAR(5)), 5) PERSISTED

Assuming that ID is an INT IDENTITY column that autogenerates sequential numbers, and you want to call your new column PKID (change as needed).

Since this is a persisted computed column, it's computed once - when the row is inserted - and it can be indexed and used as primary key.

(2) If you don't have anything like a date column in your table, then the only option would be to have a AFTER INSERT trigger on that table that does something like this (again: assuming you have a column ID INT IDENTITY to provide the auto-incremented numbers):

CREATE TRIGGER trgInsert ON dbo.YourTable 
AFTER INSERT 
AS 
BEGIN
    DECLARE @YearPrefix CHAR(2)
    SET @YearPrefix = RIGHT(CAST(YEAR(GETDATE()) AS CHAR(4)), 2) 

    UPDATE dbo.YourTable
    SET OtherID = @YearPrefix + '-' + RIGHT('00000' + CAST(i.EmployeeID AS VARCHAR(5)), 5) 
    FROM INSERTED i
    WHERE dbo.YourTable.EmployeeID = i.EmployeeID
END

And of course, first you need to add this new PKID column to your table, too, so that the trigger can store values in it :-)

ALTER TABLE dbo.YourTable
ADD PKID VARCHAR(10) NOT NULL DEFAULT ('X')  -- needs to be NOT NULL for Primary Key
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • When I change my system date e.g 2014 and I insert a new value and it will result to 14-0068 and the last record for the table is 12-0067. I want to make it 14-0001 instead of 14-0068. – keizune Feb 20 '12 at 23:25
  • @keizune: well, in that case, you cannot use an `IDENTITY` column. In SQL Server 2008 R2 or older, you'd have to do a lot of manual work to get this to run properly - or wait for SQL Server **2012** which will support `SEQUENCE` as a database object (which you could reset back to start at 1 again in the new year). – marc_s Feb 21 '12 at 05:54
  • thanks for your suggestion, but I will stick on the code that you provided. It will not be a big issue for my project. – keizune Feb 23 '12 at 15:20