Saved Broken Stored Procedure.

Question

I received a ticket today concerning a broken application that was caused by a broken stored procedure.

The stored procedure had been working for a few months now, but today when I examined it I found a few bugs.

first there was a query like this.

SELECT a.a , b.b , a.b FROM table1 a JOIN table2 b ON. a.a = b.a

the period after the "ON" obviously caused the error. Fixing it was easy, but as far as I know, you can't save a stored procedure that is broken. In fact, when I opened the stored procedure, (without modifying it) and tried to run the alter with the code from there, it failed with the error messages.

There were other errors like this through the stored procedure that I fixed to finally run the alter.

I don't understand how this is possible, and would like to know what could change and save a stored procedure that is broken. From what I've been told there have been no changes to the server at all, and like I mentioned before this stored procedure had been working for the past few months

It feels like i'm hunting a ghost, and any information will be greatly appreciated.

Answer 1

I'm guessing this is an issue of deferred name resolution.

SQL Server will allow you to create procedures that reference objects that do not exist at the time of stored procedure creation. In this case, SQL chooses to defer compilation of the query to runtime, trusting that the object will be there by the time the procedure is called, hence the syntax is not verified when the procedure is created.
If when the procedure is subsequently executed the syntax is incorrect, you'll get an error at that point, but not during the procedure creation.

For example, if table1 and table2 do not exist, this statement will complete without error:

CREATE PROCEDURE testproc AS
SELECT  a.a 
        , b.b 
        , a.b 
FROM    table1 a 
        JOIN table2 b ON. a.a = b.a

When you go to execute testproc, you'll get the following error: "The multi-part identifier ".a.a" could not be bound."