CODEWITHSUNDEEP
sql-server
Joseph Nields
Joseph Nields

Reputation: 5661

Does a stored procedure called from another block the calling procedure in SQL Server?

Going through a debug, and I'm wondering if this is the case.

Basically, this is what I have:

CREATE TABLE
    SomeTable (col INT PRIMARY KEY)
GO

CREATE PROCEDURE
    Procedure_A 
    @var int
AS
    BEGIN
        DELETE FROM 
            SomeTable
        WHERE 
            @var = col
    END
GO

CREATE PROCEDURE
    Procedure_B
    @var int
AS
    BEGIN
        EXEC Procedure_A @var
        INSERT INTO
            SomeTable (col)
        VALUES
            (@var)
    END
GO

Can I be certain that Procedure_A is completed before the insert statement is executed?

Upvotes: 0

Views: 257

Answers (1)

Brian Pressler
Brian Pressler

Reputation: 6713

The EXEC command is synchronous, which means the next statement will not start until the called procedure has completed. What could happen though is if two separate sessions call Procedure_B at nearly the same time you could run into a situation where you have an issue because there is no explicit transaction around the call to Procedure_A and your insert statement. So something like this could happen:

  • Session 1 Calls Procedure_B
  • Session 2 Calls Procedure_B with same value @var
  • Session 1 executes Procedure_A and deletes the record
  • Session 2 executes Procedure_A and the delete does nothing
  • Session 1 executes the insert
  • Session 2 executes the insert and fails with Primary Key violation.

This is called a race condition or race hazard. You could avoid this by wrapping the contents of Procedure_B in a transaction.

Upvotes: 1

Related Questions