如何用另一个存储过程执行一个存储过程?

2021-09-10 00:00:00 sql tsql sql-server

我有会话锁定程序，它执行一个程序在一个会话中运行时的操作，如果同一个程序正在另一个会话中运行，则在第一个会话完成之前它不会执行

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;

相关文章