Procedure Creation – Correct Syntax, Wrong Database

Recently, on a Friday evening, we released an update to one of our core applications.   As expected, it went smoothly and no issues were noted.  On the following Monday morning, however, the story took a different turn when end users started reporting issues.  One of the issues encountered was related to a new stored procedure.

In looking at the procedure, we found there were references to a database that existed within the QA environment but did not in Production.   It was assumed that when the procedure was created, it would throw an error because the database referenced didn’t exist.

Unfortunately, it does not work like this.

When the procedure is created, only the syntax of the code is parsed for validity.  As long as the syntax is correct the procedure will be created without an issue.  This can cause an issue because invalid references can slip by.

The real issue will manifest when the procedure is actually called.  The syntax will be checked again by the Parser and then sent to the Algebrizer (or Binding).  The invalid reference doesn’t physically exist.  This causes the Algebrizer the inability to bind and throws an error.

This is also true with altering a procedure that already exists.

We can prove this fairly easily just by creating and/or modifying a procedure with a reference to an object that does not exist.

-- We all have a Scratch Database right?
USE Scratch
GO
-- Create a procedure with invalid reference
CREATE PROCEDURE dbo.ProcCreateDemo
AS
BEGIN
   SET NOCOUNT ON;
   SELECT * FROM DBThatDoesntExist.sys.objects
END
GO
-- Does it exist?
SELECT name, object_id, type, type_desc, create_date from sys.procedures
SELECT OBJECT_DEFINITION(object_id) from sys.procedures

We can see that the procedure, even with the invalid reference, was created without issue.  If we look at the object definition of the procedure, we clearly see the invalid reference.

When the procedure is executed the error will manifest.

What if we alter an existing procedure?

In this example, I’ll adjust the procedure to take out the invalid reference.

Then I’ll alter it again to put the invalid reference back in.

Note that the procedure was altered successfully both times.  Even though the second ALTER has an invalid reference, the syntax of the command is correct thus the procedure is altered successfully.

We are currently in the process of making adjustments to our release process to ensure that issues like this are caught in the lower environments rather in Production.

References

If you want to learn more about the query optimizer, I would suggest reading this post on how the optimizer works by Benjamin Nevarez.

I would also suggest SQL Server Guru, Paul White, as he has a deep dive blog series into the query optimizer.  You can find the first post in his series here.

Enjoy!

© 2015, John Morehouse. All rights reserved.