SQL Server Error Handling within OUTER APPLY

Question

I am no expert at T-SQL and I've got an issue (or rather, I don't have an issue but I really think I should) which I hope you can shed some light on as to why.

I have a stored procedure (SQL Server 2012) which I believe should give me an error - but it doesn't. My google-fu has failed me and I have found nothing to point me in the right direction. In summary we are using the following tables:

**USERS**
UserID int
...

**HolidayYears**
HolidayYearID int
UserID int
...

**HolidayYearUserBalances**
BalanceID int
HolidayYearID int
...

The stored proc generates a report which displays users and their holiday balances in the current holiday year. To do this, I use an OUTER APPLY:

OUTER APPLY (
                SELECT TOP 1
                             HolidayYearID, 
                             YearStart, 
                             YearEnd
                FROM HRMHolidayYears 
                WHERE YearStart <= GETDATE() 
                AND 
                   UserID = Users.UserID
                ORDER BY YearStart DESC
            ) hy

This works absolutely fine, and it does exactly what I want. The problem is this:

Yesterday, this stored procedure was taken up to a customer who is running an older version of our application. In this version, a HolidayYear is not linked to a User, instead all Users share the same HolidayYear. Thus, the HolidayYears table does not have the column 'UserID'.

So my expectation is that this stored procedure would throw an error, and if I execute the SELECT contained within the OUTER APPLY in isolation, it does indeed throw the expected error (Invalid column name 'UserID').

What actually happens when you run the report is that rows are returned with no errors, however the fields related to the user's balance are NULL (as you would expect if the OUTER APPLY failed to return records for the Holiday Year, which then JOIN to the HolidayYearUserBalances table.

I have made the wild assumption that T-SQL suppresses errors within OUTER APPLY statements. Can anyone confirm or deny this, or provide any information which might help me solve this puzzle?

The reason its working is because sql is treating both UserID from the same table `Users` internally its doing something like this `Users.UserID = Users.UserID`. Change your code to `HRMHolidayYears HY WHERE YearStart <= GETDATE() AND HY.UserID = Users.UserID` and it will start failing — ughai, May 06 '15 at 09:32

score 1 · Accepted Answer · answered May 06 '15 at 11:30

From Qualifying Column Names in Subqueries:

The general rule is that column names in a statement are implicitly qualified by the table referenced in the FROM clause at the same level. If a column does not exist in the table referenced in the FROM clause of a subquery, it is implicitly qualified by the table referenced in the FROM clause of the outer query.

In your example you have a subquery (the OUTER APPLY is a red herring) and you use the implicitly qualified column UserID. According to the rule quoted above, since there is no UserID at the same level, the outer query's UserID will be implicitly used. QED.

SQL Server Error Handling within OUTER APPLY

1 Answers1