The Multi Part Identifier Could not be Bound

The Multi Part Identifier Could not be Bound

The Multi Part Identifier Could not be Bound

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.

 

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.

 

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 Star2 Stars3 Stars4 Stars5 Stars (7 votes, average: 5.00 out of 5)

Loading...

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

© SQLNetHub

 

The Multi Part Identifier Could not be Bound Click to Tweet
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server and Software 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). Artemakis's official website can be found at aartemiou.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. Tahir

    Wrongly refering a column also gives same error.

    e.g.

    Select * from EMP t

    where e.EmployeeID = 5

    would give same error.

  3. 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.

  4. 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;