如何在sql中自动生成EMPID？ [英] How to do auto generate EMPID in sql ?

问题描述

我用过这段代码但是转换失败了怎么办？我需要增加每次注册执行并产生一致性

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

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

推荐答案

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();
}

你必须转换为 nvarchar

you have to Convert as nvarchar

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

不计算NextId - 使用sql提供的内置功能 - 即使用身份 [ ^ ]栏目



这将自动增加id，更重要ly将处理多用户场景和竞争条件，远远优于找到之前MAX值的代码。



创建行时分配的值，将是该表格中独一无二。



如果您想以字符串格式查看它，请使用

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

如果出于任何原因（即我想不出一个足够好的一个），你想在插入它之前知道下一个id会是什么，然后看看 @@ IDENTITY [ ^ ]

这篇关于如何在sql中自动生成EMPID？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！