問題描述

我有會(huì)話鎖定程序，它執(zhí)行一個(gè)程序在一個(gè)會(huì)話中運(yùn)行時(shí)的操作，如果同一個(gè)程序正在另一個(gè)會(huì)話中運(yùn)行，則在第一個(gè)會(huì)話完成之前它不會(huì)執(zhí)行

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' 

它工作正常，我沒有問題，但是當(dāng)我想使用輸入?yún)?shù)執(zhí)行過程時(shí)，出現(xiàn)錯(cuò)誤:

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

錯(cuò)誤:

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

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

當(dāng)我執(zhí)行一個(gè)沒有輸入?yún)?shù)的過程時(shí)，它工作正常，當(dāng)我想執(zhí)行一個(gè)帶有輸入?yún)?shù)的過程時(shí)，它會(huì)拋出一個(gè)錯(cuò)誤.

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.

請建議如何解決這個(gè)問題.

Please suggest how to fix this.

系統(tǒng)鎖定程序:

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

推薦答案

試試這個(gè):

DECLARE @threadid INT = 0;

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

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

這篇關(guān)于如何用另一個(gè)存儲(chǔ)過程執(zhí)行一個(gè)存儲(chǔ)過程?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！