What happens if altering a stored procedure while it is running?

I've just tested this in SQL Server 2008 R2

I started with:

CREATE PROCEDURE dbo.Stupid
AS
WAITFOR DELAY '0:00:10'
SELECT TOP 5 * FROM dbo.UniqueId
GO

I then did the following SQL Server Query Window 1:

EXEC dbo.Stupid

SQL Server Query Window 2, while query in Query Window 1 was running:

ALTER PROCEDURE dbo.Stupid
AS
WAITFOR DELAY '0:00:05'
SELECT TOP 5 * FROM dbo.UniqueId
WHERE ID > 5
GO

EXEC dbo.Stupid

SQL Server Query Window 3, while queries in Query Window 1 and Query Window 2 were running:

EXEC dbo.Stupid

Results:

  • Query Window 1 ran in 10 seconds (and therefore finished after windows 2 and 3), and returned ids 1 - 5
  • Query Window 2 altered and ran the procedure in 5 seconds, and returned ids 6 - 10
  • Query Window 3 ran in 5 seconds and returned ids 6 - 10

What happens:

  • Already executing code will complete running on the procedure as it was when they were started
  • Anything that starts run after the code is altered will run the new code

When using ALTER for the procedure, a schema modification lock is set. The SP still exists, but clients will have to wait until the ALTER is executed. The same applies for ALTER, it will wait until the SP isn't used by clients.