How to do auto generate EMPID in sql ?

Question

4.00/5 (1 vote)

See more:

i used this code but convertion failed how to do ? i need increment every execution of registration and generate unicqidentity

SQL

select  'FHMS'  + max(SUBSTRING ( employeeId ,5 , 5 ))+1 as nextkey from employeemaster

Posted 20-Oct-14 18:00pm

Prasanth Radhakrishanan

Updated 21-Oct-14 0:16am

v4

Add a Solution

Comments

VC.J 21-Oct-14 0:03am

is employee id is string if yes than you are adding 1 into it first parse employeeid into int

Laiju k 21-Oct-14 0:21am

SELECT MAX(ISNULL(employeeId ,0))+1

you can append 'FHMS' afterwards.

Rajesh waran 21-Oct-14 0:44am

How u think substring(employeeId ,5 , 5) is possible for multiple entries,it will shows the same result for every repetition of execution. if the id is FHMS10001 means, the result will be 1+1=2 for every execution of this query.

5 solutions

Solution 1

Try this in C#,

SqlCommand cmd=new SqlCommand("select MAX(SUBSTRING(employeeId ,5,1000))+1 as NextKey from employeemaster",connectionstring);

SqlDataReader dr=cmd.ExecuteReader();

dr.Read();

if(dr.HasRows)
{
string str;

str="FHMS";

string id=str+dr[0].ToString();

dr.Close();
}

Posted 20-Oct-14 18:48pm

Rajesh waran

Comments

King Fisher 21-Oct-14 1:00am

don't go for Code.refer my Solution

Rajesh waran 21-Oct-14 1:06am

Ok sir,Thank You.

Solution 2

you have to Convert as nvarchar

SQL

select 'FHMS'+ cast (max(cast(SUBSTRING( employeeId ,5, 5 )as int))+1 as nvarchar) as nextkey from employeemaster

Posted 20-Oct-14 18:53pm

King Fisher

v2

Comments

Prasanth Radhakrishanan 21-Oct-14 6:17am

yes sir

Solution 3

Try This..

SQL

Use isnull(col,'Value') to set the value of the column if there is no data present into the table..

C#

select 'FHMS'+(Max(isnull((substring(employeeId ,5,len(employeeId ))),0))+1) as NextId from employeemaster

Posted 20-Oct-14 19:08pm

/\jmot

Updated 21-Oct-14 3:21am

v2

Solution 5

SQL

DECLARE @myid uniqueidentifier
SET @myid = NEWID()
select right(@myid,12) as GetID
OR
select @myid as GetID

Posted 26-Oct-14 21:37pm

vijayadav

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2014-10-21T01:32:00

Do not calculate the NextId - use the built-in functionality provided by sql - i.e. use an Identity [^]column

This will auto-increment the id and more importantly will handle multi-user scenarios and race conditions far better than your code finding the previous MAX value.

The value is assigned when you create the row, and will be unique across that table.

If you want to see it in string format just use

select 'FHMS'+ cast (employeeId nvarchar) as thisKey from employeemaster

If for whatever reason (i.e. I can't think of a good enough one), you want to know what the next id is going to be before you insert it then have a look at @@IDENTITY[^]

How to do auto generate EMPID in sql ?

5 solutions

Solution 1

Solution 2

Solution 4

Solution 3

Solution 5

Add your solution here

Preview 0

Existing Members

...or Join us