Thursday, January 29, 2009

The multi-part identifier ... could not be bound

There are cases where a database developer might get the error message "The multi-part identifier [database_entity_name] could not be bound".

This happens because of the way table scopes are handled by the developer within the query.

Let's see a relevant example:

Consider two tables; table Employee and table Address.

Employee table:CREATE TABLE [dbo].[Employee](
[id] [int] NOT NULL,
[name] [varchar](50) NULL,
[age] [int] NULL

) ON [PRIMARY]

Address table
CREATE TABLE [dbo].[address](
[empid] [int] NOT NULL,
[street] [varchar](50) NULL,
[city] [varchar](50) NULL,
[country] [varchar](50) NULL
) ON [PRIMARY]


Let's say we want to write a query returning all the employees and their country of residence sorted by the latter alphabetically.

A suggested query would be the following:

select emp.name as EmployeeName, addr.country as EmployeeCountry
from [Employee] emp
inner join [Address] addr
on emp.id=addr.empID
order by addr.country asc


Indeed, the above query works fine.

Though if someone tried to get the employees' country using a subquery like this:

select emp.name as EmployeeName, (select addr.country from [Address] addr where addr.empID=emp.id) as EmployeeCountry
from [Employee] emp
order by addr.country asc


he/she would end up with the following error message:

The multi-part identifier "addr.country" could not be bound.

The problem in the above T-SQL Statement is that even though we are using the addr table alias in the subquery, we are not syntactically allowed to use it outside the scope of the subquery, that is in this example, in the order by clause. Though the opposite is possible, that is to reference a table/alias of an outer query within an internal query (subquery). That is why in our subquery we are able to reference the emp.id table/column.

For eliminating the above error and keep on using the subquery, the correct code for this case would be:

select emp.name as EmployeeName, (select addr.country from [Address] addr where addr.empID=emp.id) as EmployeeCountry
from [Employee] emp
order by EmployeeCountry

Remarks
Even though in this example the problem was obvious, in many cases where we develop some really large and complex queries along with subqueries, we might end up consuming valuable time for resolving such issues :)

To this end we should always be careful when using subqueries in our T-SQL statements and always keep in mind that subqueries can only provide their results to their outer queries and not references to the subqueries' tables.

A future post will thoroughly explain the usage of subqueries in SQL Server.


If you are interested in SQL Server's In-Memory OLTP, check out my latest software tool "In-Memory OLTP Simulator"!
[Ads]
Check out my latest eBook on SQL Server:
Tuning SQL Server - Ebook

Easily secure your SQL Server instances! Try out DBA Security Advisor!
DBA Security Advisor for SQL Server by SQLArtBits
DBA Security Advisor was developed by SQLArtBits. The tool was being carefully developed for an entire year and it contains comprehensive security checks and recommendations based on proven SQL Server security best practices. Feel free to download and use the Community Edition which is free and if you find it useful, you can consider upgrading to the Enterprise Edition!

Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

8 comments:

SRM_BSG said...

Simply Forgeting the "FROM" part of a SQL Expression will give you the same error. I pulled half my hair out finding this simple mistake after 17 years of writing SQL statements....Ahhh!

Artemakis Artemiou [MVP] said...

Yes, that's true! :)

Well, I am quite sure that everyone of us does such mistakes sometimes!

The good thing is that in the end, finally we find and correct the mistake!

Futureproof said...

But don't forget the WHERE clause, because then you will be bald before you are 30!

Tahir said...

Wrongly refering a column also gives same error.

e.g.

Select * from EMP t

where e.EmployeeID = 5

would give same error.

Artemakis Artemiou [MVP] said...

Hi Tahir,

Well said!

Herold van de Ven said...

Make sure you have spelled the table name correctly. Misspelling the table name as in "Student.First_Name" if the table name is "Students" (with an s at the end) will also cause this error.

Unknown said...

Hello, I am learning Sql and I am stuck doing this stored procedure (see below). Can someone direct me on what I am doing wrong? I am trying to write a code that allows a user to enter business information from two separate table called Business and Employer when "HaveBusiness"? which is a table in another table called person is yes (or = 1) and then reset to Zero (or no) after the insert statements:

CREATE PROCEDURE [dbo] .[BusinessInfo]

@BusinessId int,
@PersonId int,
@BusinessName nvarchar (50),
@BizAddr nvarchar (50),
@BizCity nvarchar (10) = NULL,
@BizState nvarchar,
@BizCountryId nvarchar,
@BizFieldId int,
@BizPhone int,
@BizEmail nvarchar (30),
@BizWebsite nvarchar (50) = NULL,
@BizFax int = 0,
@DateBizStarted date,
@AboutBiz nvarchar (75)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION

If dbo.person.HaveBusiness = 1
Insert into dbo.Business (BusinessName, BizAddr, BizCity, BizState, BizCountryId, BizFieldId)
Values (@BusinessName, @BizAddr, @BizCity, @BizState, @BizCountryId, @BizFieldId)
Insert into dbo.Employer (BizPhone, BizEmail, BizWebsite, BizFax, DateBizStarted, AboutBiz)
Values (@BizPhone, @BizEmail, @BizWebsite, @BizFax, @DateBizStarted, @AboutBiz)

COMMIT TRANSACTION

Update HaveBusiness
SET HaveBusiness = 0;

Douglas Fiorio Dias said...

thanks! Simple and objective