Parsing T-SQL is not validating table schemas

I am curious why my stored procedures are parsing properly when you do not reference a table with its schema. The stored procs then fail when you run them.

It seems that the parser does not validate that a tables schema is missing.

This is an example stored procedure against the Person.Address table in the Adventureworks database. It will execute fine if I change the FROM clause to Person.Address.

CREATE PROCEDURE [dbo].[Address_Load]
@.AddressID [int]
AS
BEGIN
SET NOCOUNT ON;

DECLARE @.intError int

BEGIN TRY
SELECT A.[AddressID]
, A.[AddressLine1]
, A.[AddressLine2]
, A.[City]
, A.[StateProvinceID]
, A.[PostalCode]
FROM [Address] A
WHERE A.[AddressID] = @.AddressID

IF @.@.ROWCOUNT = 0
BEGIN
RAISERROR('Record not found', 16, 1) -- Record not found.
END

-- Return success
RETURN 0
END TRY
BEGIN CATCH
SET @.intError = ERROR_NUMBER();

-- Log error here

RETURN @.intError;
END CATCH
END

The stored proc parses fine and gets saved to the database but when executing it I get the following

Msg 208, Level 16, State 1, Procedure Address_Load, Line 10

Invalid object name 'Address'.

Is there any way to change this so the parsing will generate an error and not allow this into the database?

Thanks,

Cory

The behavior is due to deferred name resolution/compilation of TSQL modules in SQL Server. You can look it up in BOL for more details. See link below for starters:

http://msdn2.microsoft.com/en-us/library/ms190686.aspx

|||

And when you decide you don't like the behavior, please go here and vote!

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124490

It is good about 1% of the time to be like this. However, the problem is the 2% of the time when you are coding and mistype a table name and it still compiles, only to find out later when you are (hopefully) testing :)

|||Is there a way to turn off Deferred Name Resolution?|||

Nope. That is what this feedback that I mentioned:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124490

It really ought to be a settting you can ask for, not soemthing that is on all of the time.