#0292 – SQL Server – Stored Procedures are compiled when executed for the first time


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

image


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.

Advertisements

One thought on “#0292 – SQL Server – Stored Procedures are compiled when executed for the first time

  1. Pingback: #0376 – SQL Server – Msg 2714: There is already an object named ‘#tableName’ in the database. | SQLTwins by Nakul Vachhrajani

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s