Concurrency problem...............

Hello Friend

I have faced a problem in SQL Server

I have a stored procedure which works as follows

Declare @Id int

Select @id=max(id) from emp
Set @id=@id+1
update emp Set id=@id
return @id

Now, we call this stored procedure from our ASP.net application. It is working perfecly in single user environment,
but the problem is that when we are working in multi-user environment some time the procedure returns same id instead of unique id.

How can I solve it, so that every time the procedure returns unique value for each user.
Is there any technique to execute this stored procedure in queue.

Thanks in advance

That's not so much a concurrency problem as it is a misunderstanding of T-SQL.
update emp Set id = @id
This will update the ID column on all rows in table emp.
T-SQL is not shorthand for moving around resultsets.

Welcome to the forum!
The issue is in between your select and update statements, Someone else also could read the Max(ID) and update with row with the same value.You can call the select statement with HoldLock query optimization hint and keep select and Update in one transaction.
Having them in one stored procedure doesnot mean that it is in one transaction.
alternatively, You can Set the Transaction Isolation level to Serializable or Repeatable read for this storedprocedure and then move it back. I am not recommending this, unless you have issues with query hints.

I have a question on the query. Your query reads the Max(ID) from the table and then updates all rows with MaxID+1. Why should you do that? Are you trying to INSERT a row with MaxID+1? If so,Identity property is the best option for it. (you may have some missing entries if your insert fails, but for most of the cases it will work (and also reduce the cost)