The Multi Part Identifier Could not be Bound

6shares
0
2
3
1
0
0

There are cases where a SQL Server database developer might get an error message similar to: “The multi part identifier could not be bound“. This happens because of the way the database developer handles table scopes within the query. Read the article below, in order to better understand this error, and see how easy is to resolve it via a simple example.

Reproducing the Error Message

Let’s see below, a relevant example that reproduces the above error message.

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;
GO

… then he/she would end up with the following error:

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

Learn more tips like this! Check our online course!

Check our online course titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).

Learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Essential SQL Server Administration Tips (lifetime access).

Enroll Now with Discount!

Explaining and Resolving the Error

The problem in the above T-SQL Statement is that even though we used “addr” as a table alias in the subquery, we are not syntactically allowed to use it outside the scope of the subquery which, in this example, is 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;
GO

Analysis and Discussion

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.

Featured Online Courses:

Check some other related error messages and ways to resolve them:

Subscribe to our newsletter and stay up to date!

Check out our latest software releases!

Check out Artemakis’s eBooks!

Rate this article: 1 Star 2 Stars 3 Stars 4 Stars 5 Stars (7 votes, average: 5.00 out of 5)

Reference: SQLNetHub.com (https://www.sqlnethub.com)

The Multi Part Identifier Could not be Bound Click to Tweet

6shares
0
2
3
1
0
0

Artemakis Artemiou

Artemakis Artemiou is a Senior SQL Server Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks 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).

8 thoughts on “The Multi Part Identifier Could not be Bound”

SRM_BSG June 17, 2009 at 17:19

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] June 21, 2009 at 19:41

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 March 13, 2012 at 18:55

But don't forget the WHERE clause, because then you will be bald before you are 30!
Tahir August 13, 2012 at 10:57

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] August 13, 2012 at 17:51

Hi Tahir,

Well said!
Herold van de Ven September 1, 2012 at 00:31

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 July 12, 2013 at 07:39

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 July 28, 2014 at 20:55

thanks! Simple and objective