問題描述

我有會話鎖定程序，它執行一個程序在一個會話中運行時的操作，如果同一個程序正在另一個會話中運行，則在第一個會話完成之前它不會執行

I have session lock procedure which do the action of when one procedure is running in one session if the same procedure is running another session it will not execute until the first session completes

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

它工作正常，我沒有問題，但是當我想使用輸入參數執行過程時，出現錯誤:

It is working fine and I have no issues but when I want to execute procedure with input parameters, I get an error:

DECLARE @threadid INT = 0;

EXEC [util].[system-LockProcedure] N'[dbo].[usp_Test1] @threadid=@threadid', 
                       N'@threadid INT', 
                         @threadid=@threadid

錯誤:

[dbo].[usp_Test1] 需要參數 @threadid，但未提供.

[dbo].[usp_Test1] expects parameter @threadid, which is not supplied.

當我執行一個沒有輸入參數的過程時，它工作正常，當我想執行一個帶有輸入參數的過程時，它會拋出一個錯誤.

When I'm executing a procedure with no input parameters it is working fine and when I want execute a procedure with input parameters, it throws an error.

請建議如何解決這個問題.

Please suggest how to fix this.

系統鎖定程序:

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

推薦答案

試試這個:

DECLARE @threadid INT = 0;

DECLARE @ExecuteString NVARCHAR(MAX) = N'[dbo].[usp_Test1] @threadid= ' + CAST(@threadid AS VARCHAR(4)) + ';'

EXEC [util].[system-LockProcedure] @ExecuteString;

這篇關于如何用另一個存儲過程執行一個存儲過程?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！