How to lock a stored procedure without cancelling the new call
<p>I have a stored procedure that is called from a job every minute but also It can be called anytime outside the job.</p>

<p>The problem comes out if I called the stored procedure at the same time the job called it.</p>

<p>What I need is to make the second call wait until the first finish then the second call start.</p>

<p>I followed this <a href="http://technico.qnownow.com/how-to-prevent-stored-procedure-running-concurrently-in-sql-server/" rel="nofollow">Link</a> to use sp_getapplock.</p>

<p>Note: I tried sp_getapplock but it cancels the second call.</p>

<p>Update:
  This is a sample of what I'm using</p>

<pre><code>ALTER PROCEDURE TestingLocking
AS  
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    DECLARE @returnCode INT, @DriverID INT, @OrderID int, @CarTypeID int

    EXEC @returnCode = sp_getapplock
            @Resource = 'TestingLocking',
            @LockMode = 'Exclusive',
            @LockOwner = 'Session',
            @LockTimeout = 5,
            @DbPrincipal  = 'public'

    DECLARE db_cursor CURSOR FOR 
        SELECT top 5 OrderID, CarTypeID FROM Orders

    OPEN db_cursor   
        FETCH NEXT FROM db_cursor INTO @OrderID, @CarTypeID
        WHILE @@FETCH_STATUS = 0 
    BEGIN
        --Update Orders Set DriverID = @DriverID WHERE OrderID = @OrderID
    WAITFOR DELAY '00:00:02';
        --INSERT INTO Logs(OrderID,DriverID) Values(@OrderID,@DriverID)

        FETCH NEXT FROM db_cursor INTO @OrderID, @CarTypeID
    END
    CLOSE db_cursor
    DEALLOCATE db_cursor

    EXEC @returnCode = sp_releaseapplock
                        @Resource = 'TestingLocking',                       
                        @LockOwner = 'Session',
                        @DbPrincipal  = 'public'

END
GO
</code></pre>

<p>When I execute the stored from two instances of SSMS, the second one raise an error </p>

<blockquote>
  <p>Cannot release the application lock (Database Principal: 'public',
  Resource: 'TestingLocking') because it is not currently held.</p>
</blockquote>