In any practice, misconceptions are always around. In the case of SQL Server, one of the misconceptions that never seems to go away is that stored procedures are always pre-compiled and ready for use.

I recently ran into a developer who was scratching his head because SQL Server did not prevent him from deploying a stored procedure which clearly had a script that would fail to parse. I plan to use today’s post to prove to anyone I come across who harbours this myth that:

Stored procedures are not compiled at creation time – they are compiled during the first execution.

The proof

In the script below, I create a stored procedure that attempts to select data from a table that does not exist. Notice that Intellisense is clearly telling me that the table does not exist.

USE tempdb;
GO
IF OBJECT_ID('dbo.proc_WhenAreProceduresCompiled','P') IS NOT NULL
    DROP PROCEDURE dbo.proc_WhenAreProceduresCompiled;
GO

CREATE PROCEDURE dbo.proc_WhenAreProceduresCompiled
AS
BEGIN
    SET NOCOUNT ON;

    SELECT *
    FROM dbo.SomeTableThatDoesNotExist;

    --The following can also be used for the test
    --SELECT 1/0
END;
GO

Executing this script gives the first confirmation – the stored procedure is created successfully, and it can be confirmed by the fact that we get a valid OBJECT_ID() value:

USE tempdb;
GO
--Confirm that the Stored Procedure is indeed created
SELECT OBJECT_ID('dbo.proc_WhenAreProceduresCompiled','P') AS ObjectId;
GO

/************************
ObjectId
-----------
821577965
************************/

Attempting to execute the stored procedure, however fails with the missing object error.

USE tempdb;
GO
--Attempt to execute the stored procedure
EXEC dbo.proc_WhenAreProceduresCompiled;
GO

Msg 208, Level 16, State 1, Procedure proc_WhenAreProceduresCompiled, Line 7
Invalid object name ‘dbo.SomeTableThatDoesNotExist’.

This test is a basic confirmation of the fact that stored procedures are compiled when they are executed for the first time, not at the time of object creation.

Until we meet next time,

Be courteous. Drive responsibly.