Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Running only one instance of Sql Server stored procedure
if not exists (select * from dbo.sysobjects
where id = object_id(N'dbo.p_AcquireLock') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
exec('create procedure dbo.p_AcquireLock as select 1')
go
alter procedure dbo.p_AcquireLock
(
@LockName nvarchar(255)
)
as
begin
declare @i int
declare @lckName nvarchar(100)
set @lckName='ServiceBook->'+@LockName
exec @i=sp_getapplock @lckName,'Exclusive','Session',0
select @i as LockStatus
return @i
end
go
-- Usage
exec @i=dbo.p_AcquireLock @MyProcName
if @i < 0
begin
print 'Another instance is active'
return
end
go
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment