SQL Server and .NET Troubleshooting Articles

The Multi Part Identifier Could not be Bound

by

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 new Online Course!

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

Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

Essential SQL Server Development Tips for SQL Developers - Online Course

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 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
Views: 7,475

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

  1. 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!

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

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

Comments are closed.