CODEWITHSUNDEEP
sqlsql-server-2008
c.jack
c.jack

Reputation: 375

Auto Increment Sql Query

i am working on case monitoring system in which i need to make query which will automatic generate case no. +1 to the previous case entered. Please have a look on my query

select  'A/' +right(cast(year(GETDATE()) as CHAR(4)), 2)+'/'+ + cast(max(right((CaseNo), 2) )+1 as varchar(50))as caseno from tbl_RecordRequisition

it is working fine but after case no. 100 it is not incrementing to 101.

please help

Upvotes: 1

Views: 1113

Answers (2)

marc_s
marc_s

Reputation: 755491

One word of advice: don't try to do this manually.

The only viable solution is to use

  • an ID INT IDENTITY(1,1) column to get SQL Server to handle the automatic increment of your numeric value
  • a computed, persisted column to convert that numeric value to the value you need

So try this:

CREATE TABLE dbo.Cases
  (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
   CaseID AS 'A/' + RIGHT('00000' + CAST(ID AS VARCHAR(5)), 5) PERSISTED,
   .... your other columns here....
  )

Now, every time you insert a row into Cases without specifying values for ID or CaseID:

INSERT INTO dbo.Cases(Col1, Col2, ..., ColN)
VALUES (Val1, Val2, ....., ValN)

then SQL Server will automatically and safely increase your ID value, and CaseID will contain values like A/00001, A/00002,...... and so on - automatically, safely, reliably, no duplicates.

Update: if you have a DATE or DATETIME column in your table, and you want to include the last two digits of that date's year into your computed column, use this formula:

CREATE TABLE dbo.Cases
  (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
   CaseDate DATE DEFAULT(GETDATE()),
   CaseID AS 'A/' + CAST(YEAR(CaseDate) AS VARCHAR(2)) + '/' + 
             RIGHT('00000' + CAST(ID AS VARCHAR(5)), 5) PERSISTED,
   ... (other columns) .....
  )

You cannot use CAST(YEAR(GETDATE()) .... since that would make the column non deterministic, and then you cannot persist (store) the value anymore - it would have to be calculated each time you access it, which is something I'd try to avoid if ever possible

Upvotes: 2

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28423

Try like this

You can use row_number() to generate AutoNumber

Eg:

SELECT row_number() OVER (ORDER BY casno) n,
       casno, 
FROM Table1

EDIT

SELECT *,row_number(Partition By id,CaseNo) OVER (ORDER BY id) AS CASENO
FROM tbl_RecordRequisition

Upvotes: 1

Related Questions