Lock Stored Procedure

Hello,

Can we lock stored procedure until its execution is complete ?
I dont want 2 clients to simultenously execute the stored procedure in
Sql Server 2000.
My front end is ASP.net 1.0

Thanks in advance.
waiting for the reply.
A.V.C. wrote:

> Hello,
> Can we lock stored procedure until its execution is complete ?
> I dont want 2 clients to simultenously execute the stored procedure in
> Sql Server 2000.
> My front end is ASP.net 1.0
> Thanks in advance.
> waiting for the reply.

Hi. I don't know if there is a better way, but you can certainly
make it lock a specific datum as it's first step, and release it
at the end. This way any second user running the procedure will hang
at the first step till the first runner is finished:
Pseudocode:

create proc myproc as
begin
begin tx
update my_single_row_proc_lock_table set val = 0 -- locks the one row in the table

...do all you want...

commit tran
end|||[posted and mailed, please reply in news]

A.V.C. (yhspl_softwaregroup@.hotmail.com) writes:
> Can we lock stored procedure until its execution is complete ?
> I dont want 2 clients to simultenously execute the stored procedure in
> Sql Server 2000.

Application locks might be what you are looking for. Check out the
stored procedure sp_getapplock in Books Online.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp