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.
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,