T-SQL error: "The multi-part identifier X.Y could not be bound"

Question

I am running a dynamic SQL which is giving me an error about "multi-part identifiers". As I have identically-named columns in different tables, I am prefixing the column name with a table alias, which seems to be the problem.

The reported errors are:

The multi-part identifier e.Categorydescription could not be bound.
The multi-part identifier l.FullAddress could not be bound.

The column Categorydescription exists in tables Events and Categories, and column FullAddress exists in tables Events and Location, hence I am using e.Categorydescription and l.FullAddress. I cannot drop the inner joins as I need other columns from the tables Location and Categories.

I misjudged the error. The actual error is further down (below) in the code where I read from ##Results global table:

SET @s_query =  'SELECT ' + @ColNames + ' FROM ##Results
    WHERE ##RowNum BETWEEN('+CONVERT(varchar(20),@PageIndex)+'-1) * '+
    CONVERT(varchar(20),@PageSize)+' + 1 
    AND((('+CONVERT(varchar(20),@PageIndex)+' -1) * '+
    CONVERT(varchar(20),@PageSize)+' + 1) + '+CONVERT(varchar(20),@PageSize)+') - 1
    ORDER BY ##RowNum';

EXEC (@s_query); -- the error is from here 
--because #Results# has "Categorydescription" instead of "e.Categorydescription"

shoule e.CategoryDescription be c.CategoryDescription. It would help if you added table structures. — Steve Ford, Aug 29 '14 at 09:20
@user2906420: I have abbreviated your question to make it easier & quicker to read. You've showed with your `PRINT` output what actual SQL query gets executed, so I have removed the irrelevant bits (where you build the SQL query from several variables) and substituted the actual SQL. (Feel free to roll back my edit if you disagree with it.) — stakx - no longer contributing, Aug 29 '14 at 09:29
I am curious about your `SELECT` column `##RowNum`. I understand [what `##` means in table names (global temporary tables)](http://stackoverflow.com/a/3166130/240733), but with column names…? Is this a valid column name, or do you need to **(a)** remove the `##`; or **(b)** escape the column name by putting it in angle brackets: `[##RowNum]`? — stakx - no longer contributing, Aug 29 '14 at 09:35
i'll update above with table structure. @stakx yes it is a valid column as i have used the same code above with other stored procedures which do not have identical column names in tables. so the above code structure works fine but having a problem with multi-part identifiers. — user2906420, Aug 29 '14 at 09:53
@SteveFord I tried changing e.Categorydescription with cat.Categorydescription but same error — user2906420, Aug 29 '14 at 09:56
The usual way to configure SQL Server is to be case-insensitive, but it's possible that it's not in your case. Is the casing of the names you're using here *exactly* the same as the case used in the tables? — Damien_The_Unbeliever, Aug 29 '14 at 09:59
I misjudged the error. The actual error is further down in the code where I read from ##Results table. Pls see updated question above: — user2906420, Aug 29 '14 at 11:36

score 2 · Accepted Answer · answered Aug 29 '14 at 13:42

Can't post comments yet, sorry.

@ColNames - what is in this? e.categorynames or categorynames ?

I'm going to assume you've got e.categorynames and l.fulladdress in here. SQL Server absolutely needs the multipart reference in the select due to the same column existing in two places, however the resulting column would just be the Columnname. You can prove this by only running part of the query as a standard select

Select   Balance_Date,
       FullName,
       e.Categorydescription,
       l.FullAddress
From [Events] e
inner join Person c ON e.Pid = c.Pid
inner join Categories cat ON e.Cid = cat.Cid
inner join Location l ON e.Lid = l.Lid';

This should display column names of Balance_Date, FullName, CategoryDescription and FullAddress (ie without the multipart identifier on the front).

Drop the multipart and just reference them as categorynames and fulladdress or even better re-alias them so you know, 100%, what the names of the columns within ##results will be.

SET @s_query = '
    Select ROW_NUMBER() OVER (ORDER BY Balance_Date asc) AS ##RowNum,
       Balance_Date AS Balance_Date,
       FullName AS Fullname,
       e.Categorydescription AS CategoryDescription,
       l.FullAddress AS FullAddress
INTO ##Results
From [Events] e
inner join Person c ON e.Pid = c.Pid
inner join Categories cat ON e.Cid = cat.Cid
inner join Location l ON e.Lid = l.Lid';

then make sure @ColNames contain these aliases.

T-SQL error: "The multi-part identifier X.Y could not be bound"

1 Answers1