The multi-part identifier … could not be bound

The multi-part identifier … could not be bound

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
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“!


Recommended eBooks on SQL Server:

Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found atย aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.comย and TechHowTos.com.

8 thoughts on “The multi-part identifier … could not be bound

  1. SRM_BSG

    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!

  2. Herold van de Ven

    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.

  3. Unknown

    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;