CTE生成“多部分标识符无法绑定”

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

我有一个使用CTE的存储过程,它工作正常。我最近搬到了一个新的服务器,现在它启动了一个错误

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

无法绑定多部分标识符“DOWNLOADS_downloadCategoryLink.categoryID”

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?

我可以通过将数据库的兼容级别更改为80(SQL Server 2000)来解决此问题,但我更愿意正确解决问题 - 任何人都有任何想法我会出错?

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

1 个解决方案

#1

Your dynamic SQL generates a query like

您的动态SQL生成一个类似的查询

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.

引用查询中不存在的表DOWNLOADS_downloadCategoryLink。我对查询曾经工作感到惊讶,但也许有一些解析器错误已被修复。

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

要正确解决这个问题,我们需要知道table categoryID实际属于哪个。

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

#1