当StoreProcedure没有Input参数时，sp_getapplock正在工作 [英] sp_getapplock is working when StoreProcedure have no Input parameters

问题描述

我一直在使用这个sp_getapplock来锁定资源的会话。以下是程序：

I have been using this sp_getapplock to lock session of the resource . Below is the Procedure :

CREATE Procedure [dbo].[system-LockProcedure] @procname varchar(200)

AS 

--BEGIN TRAN
BEGIN

DECLARE @lockreturn int,
        @lockresource varchar(200)               --sysname


SELECT @lockresource =    @procname    



              -- The below line will try to acquire an exclusive lock on the PROC for the session, If the Proc is already in execution the @lockreturn value will be > 0

                 EXEC @lockreturn = sp_getapplock @lockresource, @LockMode = 'Exclusive', @LockOwner = 'Session'  , @LockTimeout = 100
                                                                Print @lockreturn 

                 IF @lockreturn <> 0
                 BEGIN

                        RAISERROR ('Another instance of the procedure is already running', 16, 1)
                                                                                                RETURN
                 END

                -- The Code to be executed goes here. All the core logic of the proc goes here..

              Print 'Procedure Execution Started for user: ' +  cast (CURRENT_USER as nvarchar(20))+ ' for session: ' + cast (@@SPID as nvarchar(10))

                -- This is just to make the system wait for 30 seconds and make sure if there is any concurrent execution triggered will fail

                                                                exec @lockresource

              Print 'Procedure Execution Ended for user: ' +  cast (CURRENT_USER as nvarchar(20))+ ' for session: ' + cast (@@SPID as nvarchar(10))

                                                  Print @lockreturn

                -- This is to release the lock once the SP code is ran completely

                                                EXEC sp_releaseapplock @lockresource , 'Session'

                                                 -- END TRY


    END

我们可以执行你本地的这个程序也要进行测试。

We can execute this procedure in your local also to test.

当我在一个会话中执行该程序时，如果有人想要执行这个存储过程，那么它将显示已经打开的消息其他会议。使用资源名称执行此过程时，也是过程对象

When I'm executing the procedure in one session if some one wants to execute this Store Procedure then it will shows message that it is already opened in other session. When executing this procedure with resource name which is also procedure Object

EXEC dbo.[system-LockProcedure]'dbo.usp_Test1'

它是工作正常

EXEC dbo.[system-LockProcedure]'dbo.usp_Test2'

有输入参数并且要求输入参数

which have input parameters and it is asking for input parameter

Error  : 
Msg 201, Level 16, State 4, Procedure USP_Test2, Line 23
Procedure or function 'USP_Test2' expects parameter '@ThreadID', which was not supplied.

推荐答案

EXEC
[ dbo ]。[ system-LockProcedure ]
'[dbo]。[USP_Test2] 1' ;

EXEC [dbo].[system-LockProcedure] '[dbo].[USP_Test2] 1';

应该工作

这篇关于当StoreProcedure没有Input参数时，sp_getapplock正在工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！