some kind of lock in procedure to stop duplicate entry?

Posted by Modit under Sql Server on 1/17/2013 | Points: 10 | Views : 694 | Status : [Member] | Replies : 1
i am creating a procedure which will generate a unique id of a visitor, and will be insterted in a table.

Table : details
Field : Uid

create procedure p1
declare @id varchar(100)
declare @tot int
select @tot=count(*) from details
set @id='P0'+cast((@tot+1) as varchar(100))
else if(@tot<99)
set @id='P'+cast((@tot+1) as varchar(100))
insert into details values (@id)

the main problem that can occur is maybe two or more users can request the procedure from my front end application at exact same time, so it may be a possibility that both of them can get the same uid, i could have used primarykey to stop same data to insert but that will throw an exception.

Is there some kind of lock which can be implemented in my stored procedure so that only a single user is given right to access the procedure and rest of requests are kept in a queue.



Posted by: Shubham on: 1/17/2013 [Member] Starter | Points: 25

yes modit before inserting pls run select query and check the id ability if hasrow going to true so stop the method!!!!!

Shubham Choudhary
Software Engineer

Modit, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response