title: sp_getapplock is working when stored procedure has no input parameters
body: <p>I have been using this <code>sp_getapplock</code> to lock session of the resource.</p>
  
  <p>This is the stored procedure: </p>
  
  <pre><code>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 &gt; 0
      EXEC @lockreturn = sp_getapplock @lockresource, 
                                       @LockMode = 'Exclusive', 
                                       @LockOwner = 'Session', 
                                       @LockTimeout = 100
  
      PRINT @lockreturn 
  
      IF @lockreturn &lt;&gt; 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
  </code></pre>
  
  <p>We can execute this procedure in your local also to test.</p>
  
  <p>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.</p>
  
  <p>When executing this procedure with resource name which is also procedure object </p>
  
  <pre><code>EXEC dbo.[system-LockProcedure]'dbo.usp_Test1'
  </code></pre>
  
  <p>it is working fine </p>
  
  <pre><code>EXEC dbo.[system-LockProcedure]'dbo.usp_Test2'
  </code></pre>
  
  <p>which have input parameters and it is asking for input parameter </p>
  
  <p>Error: </p>
  
  <blockquote>
    <p>Msg 201, Level 16, State 4, Procedure USP_Test2, Line 23<br>
    Procedure or function 'USP_Test2' expects parameter '@ThreadID', which was not supplied.</p>
  </blockquote>