0

I have been using this sp_getapplock to lock session of the resource.

This is the stored 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 stored procedure has run 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 stored procedure, then it will show 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'

it is working fine 

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mohan111
  • 8,633
  • 4
  • 28
  • 55
  • 1
    What is the source of the `@TreadID` parameter value needed by `USP_Test2`? Perhaps you need to pass it as a parameter to `[dbo].[system-LockProcedure]`. If your intent is a generic wrapper proc to ensure only one instance of the called proc can run at a time, you'll need to pass the proc name (for the lock resource) and required parameters separately. That will be a challenge if the number, name and parameter types of the called proc vary. It would be much easier for the caller to handle the locking, perhaps using utility procs that wrap `sp_getapplock` and `sp_releaseapplock`. – Dan Guzman Jun 07 '19 at 10:44
  • @DanGuzman ThreadID is just a input parameter in USP_Test2 procedure used in WHERE condition . So when I call the Procedure which doesn't have Input parameters it is working but when I call Procedure with Input then it is asking for input parameter id – mohan111 Jun 07 '19 at 10:50
  • What I mean is that `system-LockProcedure` will need a `@ThreadID` parameter and pass that parameter on the `EXEC` statement when it calls `USP_Test2`. You are getting the error because `@ThreadID` is missing on the `EXEC` statement. – Dan Guzman Jun 07 '19 at 10:58
  • @DanGuzman how I need to handle this can you suggest me – mohan111 Jun 07 '19 at 11:22
  • I suggested how to handle this in my earlier comment; have the caller handle the locking and call the proc directly. – Dan Guzman Jun 07 '19 at 11:24
  • @DanGuzman I have never worked on caller handle it will be helpful if you post an example or solution – mohan111 Jun 07 '19 at 11:35
  • And another rude/wasteful duplicate post on [msdn](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/00c2a318-23f5-4af5-acf5-a5a3a968af94/spgetapplock-is-working-when-storeprocedure-have-no-input-parameters?forum=transactsql) – SMor Jun 07 '19 at 12:13
  • Possible duplicate of [How to execute a stored procedure with another stored procedure?](https://stackoverflow.com/questions/56487871/how-to-execute-a-stored-procedure-with-another-stored-procedure) – SMor Jun 07 '19 at 12:14
  • @SMor I'm not getting proper answer so I have looked for other sources as well..how can you say wasteful and rude ...completely this one is against the SO Ethics code ... – mohan111 Jun 07 '19 at 12:56
  • @SMor if you don't have that capacity to answer the question wait for the people who can answer so you can also learn from that ...stop being a nuisance in SO. – mohan111 Jun 07 '19 at 12:57

0 Answers0