2

I got a value in a field called it (EmployeeDetailKey - varchar(10)) with sequential values such as

00001, 00002, 00003....

It is in a table Employeedetail. When ever a new Employee detail has to be inserted I have to get the max(EmployeeDetailKey) and increment by 1 and store it back. If I have 10 employeedetail records that need to be inserted then the same procedure has to follow.

If the max(EmployeeDetailKey) = 00003 then after inserting 10 records it has to be 00013. Later on after inserting let us say 100 records it has to be 00113.

How can I do it in the form of MS-SQL statement.

Please note the column cannot be identity type.

Sreedhar Danturthi
  • 7,119
  • 19
  • 68
  • 111
  • are gaps allowed/permitted? what have to happen when (not 'if', 'when') a row will be deleted? which version of sql server? are you allowed to change the column type and use numeric data types? – Paolo Jul 16 '15 at 11:38
  • yes gaps are allowed. For example when employee leaves the organization that employee detail is deleted but when the employee details need to be added it has to follow a sequence. The version i have is SQL-Server 2008. – Sreedhar Danturthi Jul 16 '15 at 11:41
  • 2
    What if the last added employee leaves the organization? A simple "+1" approach would use the same number once again... Very dangerous... – Shnugo Jul 16 '15 at 11:53
  • what's the use of a sequence then? with gaps and deletion it has no added value. why not just use a `GUID` or any other random ID that's easier to manage? – Paolo Jul 16 '15 at 12:04

3 Answers3

4

Just add an identity column to your table. I would suggest something like:

IntEmployeeDetailKey int not null identity(1, 1) primary key,
. . .

Then add a computed column:

EmployeeDetailKey as (right(('00000' + cast(IntEmployeeDetailKey as varchar(10)), 5)

Then SQL Server will do the incrementing automatically. And you can get the value out as a zero-padded string.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

If you prefer a solution without changing the table structure, then:

  1. Cast your zero-padded string value to int. This is easy in SQl server, as it will easily convert such strings to numbers:

    SELECT CAST('00003' AS int)

    This will return integer value of 3.

  2. Find MAX()

    Just perform MAX() on column you've just converted to string, like...

    SELECT MAX(CAST(mycolumn AS int)) FROM mytable

    Actually, you don't have to do a conversion, as SQL server will sort the values correctly in original string representation.

  3. Increment

    This is easy, since you now have the integer value, so...

    SELECT MAX(CAST(mycolumn AS int)) + 1 FROM mytable

  4. Convert it back to zero-padded string

    SQL Server 2008 is a bit tricky to tame here, since left-padding is not his speciality. However, starting from in SQL Server 2012, there is a FORMAT function available, so, you can use...

    SELECT FORMAT(MAX(CAST(mycolumn AS int)) + 1, '00000') FROM mytable

    If you have only SQL Server 2005 or 2008 available, you can use REPLICATE() combined with LEN() to get what you need (disclaimer: UGLY CODE):

    SELECT REPLICATE('0', 5 - LEN(MAX(CAST(mycolumn AS int)) + 1)) + CAST((MAX(CAST(mycolumn AS int)) + 1) AS nvarchar(5)) FROM mytable

    EDIT As Luaan hinted, you can use another padding option (shorter and more readable code):

    SELECT RIGHT('00000' + CAST(MAX(CAST(mycolumn AS int) + 1) as nvarchar(5)), 5) FROM mytable

OzrenTkalcecKrznaric
  • 5,535
  • 4
  • 34
  • 57
  • 2
    There's a nicer way on pre-2012 - `right('00000' + value, 5)`. I'm not aware of any performance implications (it's never been a bottleneck for me), but we're doing serializable updates for the autoincrement anyway, so... – Luaan Jul 16 '15 at 11:48
  • 1
    @Luaan: great, I've included that solution in the answer – OzrenTkalcecKrznaric Jul 16 '15 at 11:55
0

I don't know if it is mandatory that EmployeeDetailKey must have this format.

I'll suggest you to substitute the datatype from varchar(10) to IDENTITY.

IDENTITY is a sort of special data type that gets automatically incremented by SQL Server each time you insert a row in the parent table. You can learn more here: https://msdn.microsoft.com/en-us/library/ms186775.aspx

If you need to present it with leading zeroes you can always select it like this (liberally adapted from here: Most efficient T-SQL way to pad a varchar on the left to a certain length?):

SELECT right('00000'+ rtrim(EmployeeDetailKey), 5) FROM YourTable
Community
  • 1
  • 1
sblandin
  • 904
  • 4
  • 11
  • 25