CTE generating "multi-part identifier could not be bound"

Question

I have a stored procedure that uses a CTE and it has worked fine. I recently moved to a new server however and now it kicks up an error

The multi-part identifier "DOWNLOADS_downloadCategoryLink.categoryID" could not be bound

I can fix this by changing the compatibility level of the database to 80 (SQL Server 2000) but I'd prefer to fix the problem properly - anyone have any ideas where I'm going wrong?

The stored procedure is:

;WITH CTE( CategoryID, ParentCategoryID )
AS
(
    SELECT CategoryID, ParentCategoryID
    FROM DOWNLOADS_fileCategories
    WHERE CategoryID = @startCategoryID

    UNION ALL

    SELECT a.CategoryID, a.ParentCategoryID
    FROM DOWNLOADS_fileCategories a
    INNER JOIN CTE b ON b.CategoryID = a.ParentCategoryID
)
SELECT CategoryID INTO #tempLinkTable FROM CTE

set @query = 'SELECT TOP ' + cast(@noRecordsToReturn as varchar(5)) + ' DOWNLOADS_files.downloadID, DOWNLOADS_files.fileTypeID, DOWNLOADS_files.downloadName, DOWNLOADS_files.downloadFileName, DOWNLOADS_files.sizeInKb, DOWNLOADS_files.dateAdded, DOWNLOADS_files.visible, SYS_fileTypes.fileTypeName, SYS_fileTypes.fileTypeExtension
FROM DOWNLOADS_files LEFT OUTER JOIN SYS_fileTypes ON DOWNLOADS_files.fileTypeID = SYS_fileTypes.fileTypeID INNER JOIN
                  #tempLinkTable ON DOWNLOADS_downloadCategoryLink.categoryID = #tempLinkTable.categoryID
ORDER BY DOWNLOADS_files.dateAdded DESC'

exec(@query)

The tables I have are:

DOWNLOADS_files

downloadID
downloadFileName

DOWNLOADS_fileCategories

categoryID
parentCategoryID
categoryName

DOWNLOADS_fileCategoryLink

categoryID
downloadID

Many thanks!

Bob

Answer 1

Your dynamic SQL generates a query like

SELECT TOP 10 DOWNLOADS_files.downloadID,
              DOWNLOADS_files.fileTypeID,
              DOWNLOADS_files.downloadName,
              DOWNLOADS_files.downloadFileName,
              DOWNLOADS_files.sizeInKb,
              DOWNLOADS_files.dateAdded,
              DOWNLOADS_files.visible,
              SYS_fileTypes.fileTypeName,
              SYS_fileTypes.fileTypeExtension
FROM   DOWNLOADS_files
       LEFT OUTER JOIN SYS_fileTypes
         ON DOWNLOADS_files.fileTypeID = SYS_fileTypes.fileTypeID
       INNER JOIN #tempLinkTable
         ON DOWNLOADS_downloadCategoryLink.categoryID = #tempLinkTable.categoryID
ORDER  BY DOWNLOADS_files.dateAdded DESC 

The inner join condition

   INNER JOIN #tempLinkTable
     ON DOWNLOADS_downloadCategoryLink.categoryID = #tempLinkTable.categoryID

references a table DOWNLOADS_downloadCategoryLink that doesn't exist in the query. I'm surprised the query ever worked but maybe there was some parser bug that has since been fixed.

To fix this properly we would need to know what table categoryID actually belongs to.

Edit: Maybe

SELECT TOP (@noRecordsToReturn) DOWNLOADS_files.downloadID,
                                DOWNLOADS_files.fileTypeID,
                                DOWNLOADS_files.downloadName,
                                DOWNLOADS_files.downloadFileName,
                                DOWNLOADS_files.sizeInKb,
                                DOWNLOADS_files.dateAdded,
                                DOWNLOADS_files.visible,
                                SYS_fileTypes.fileTypeName,
                                SYS_fileTypes.fileTypeExtension
FROM   DOWNLOADS_files
       INNER JOIN DOWNLOADS_downloadCategoryLink
         ON DOWNLOADS_downloadCategoryLink.downloadID = DOWNLOADS_files.downloadID
       INNER JOIN #tempLinkTable
         ON DOWNLOADS_downloadCategoryLink.categoryID = #tempLinkTable.categoryID
       LEFT OUTER JOIN SYS_fileTypes
         ON DOWNLOADS_files.fileTypeID = SYS_fileTypes.fileTypeID
ORDER  BY DOWNLOADS_files.dateAdded DESC