【100分】我在执行翻页的存储过程中出现“无法绑定由多个部分组成的标识符”

[look4u]

我的存储过程是这样的：我用一个临时表做的翻页
ALTER PROCEDURE [dbo].[WF_Statistatics_ResourceUseStats]
@startDate datetime,
@endDate datetime,
@resIDs varchar(8000),
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1 -- 页码
AS
declare @strSQL1 varchar(4000) -- 主语句
DECLARE @PageLowerBound varchar(30)
DECLARE @PageUpperBound varchar(30)
SET @PageLowerBound = convert(varchar,@PageSize * @PageIndex)
SET @PageUpperBound = convert(varchar,@PageSize - 1 + @PageLowerBound)


CREATE TABLE #PageIndexTable
(
IndexId int IDENTITY (0, 1) NOT NULL,
IndexField varchar(300)
)

-- Insert into our temp table
set @strSQL1='INSERT INTO #PageIndexTable (IndexField) SELECT rs_id from WF_RS'
exec(@strSQL1)
/*统计全部*/
IF (@ResIDs IS NULL)
BEGIN
SELECT r.RS_ID AS ResID
,r.RS_Name AS ResName
,ISNULL(SearchAmount,0) AS SearchAmount
,ISNULL(TrackAmount,0) AS TrackAmount
,ISNULL(BrowseAmount,0) AS BrowseAmount
,ISNULL(DownloadAmount,0) AS DownloadAmount
,ISNULL(OriRequestAmount,0) AS OriRequestAmount
FROM WF_RS r, #PageIndexTable p
LEFT JOIN /*检索次数*/
(
SELECT COUNT(sl.SearchLogID) AS SearchAmount
, ResID
FROM WF_SearchLog sl
INNER JOIN WF_SearchResLog srl ON sl.SearchLogID = srl.SearchLogID
INNER JOIN WF_SearchRes sr ON srl.SearchResID = sr.SearchResID
WHERE (sl.SearchDateStamp BETWEEN @startDate AND @endDate)
GROUP BY ResID
) s ON r.RS_ID = s.ResID
LEFT JOIN /*点击量*/
(
SELECT COUNT(RESID) AS TrackAmount
,ResID
FROM ResTrackLog
WHERE (TrackDate BETWEEN @startDate AND @endDate)
GROUP BY ResID
) tl ON r.RS_ID = tl.ResID
LEFT JOIN /*浏览次数*/
(
SELECT COUNT(*) BrowseAmount, RS_ID
FROM WF_Browse_log
WHERE Browse_Time BETWEEN @startDate AND @endDate
GROUP BY RS_ID
) B ON r.RS_ID = B.RS_ID
LEFT JOIN /*下载次数*/
(
SELECT COUNT(ResID) DownloadAmount, ResID
FROM ResDownloadLog
WHERE DownloadDate BETWEEN @startDate AND @endDate
GROUP BY ResID
) D ON r.RS_ID = D.ResID
LEFT JOIN /*消费金额*/
(
SELECT SUM(OrderMoney) AS OriRequestAmount
, ResID
FROM WF_OrderDtl
GROUP BY ResID
) O ON r.RS_ID = o.ResID
where r.RS_ID=p.IndexField and p.IndexId >='+@PageLowerBound+' AND p.IndexId <='+@PageUpperBound'
ORDER BY ResName
END
ELSE
BEGIN
SELECT r.RS_ID AS ResID
,r.RS_Name AS ResName
,ISNULL(SearchAmount,0) AS SearchAmount
,ISNULL(TrackAmount,0) AS TrackAmount
,ISNULL(BrowseAmount,0) AS BrowseAmount
,ISNULL(DownloadAmount,0) AS DownloadAmount
,ISNULL(OriRequestAmount,0) AS OriRequestAmount
FROM WF_RS r , #PageIndexTable p
INNER JOIN UDF_iter_charlist_to_table(@ResIDs,default) ids ON r.RS_ID = ids.nstr
LEFT JOIN /*检索次数*/
(
SELECT COUNT(sl.SearchLogID) AS SearchAmount
, ResID
FROM WF_SearchLog sl
INNER JOIN WF_SearchResLog srl ON sl.SearchLogID = srl.SearchLogID
INNER JOIN WF_SearchRes sr ON srl.SearchResID = sr.SearchResID
WHERE (sl.SearchDateStamp BETWEEN @startDate AND @endDate)
GROUP BY ResID
) s ON r.RS_ID = s.ResID
LEFT JOIN /*点击量*/
(
SELECT COUNT(RESID) AS TrackAmount
,ResID
FROM ResTrackLog
WHERE (TrackDate BETWEEN @startDate AND @endDate)
GROUP BY ResID
) tl ON r.RS_ID = tl.ResID
LEFT JOIN /*浏览次数*/
(
SELECT COUNT(*) BrowseAmount, RS_ID
FROM WF_Browse_log
WHERE Browse_Time BETWEEN @startDate AND @endDate
GROUP BY RS_ID
) B ON r.RS_ID = B.RS_ID
LEFT JOIN /*下载次数*/
(
SELECT COUNT(ResID) DownloadAmount, ResID
FROM ResDownloadLog
WHERE DownloadDate BETWEEN @startDate AND @endDate
GROUP BY ResID
) D ON r.RS_ID = D.ResID
LEFT JOIN /*消费金额*/
(
SELECT SUM(OrderMoney) AS OriRequestAmount
, ResID
FROM WF_OrderDtl
GROUP BY ResID
) O ON r.RS_ID = o.ResID
WHERE r.State=1 AND r.Enable=1
ORDER BY ResName
END

我在2005中是这样运行的：
DECLARE @return_value int

EXEC @return_value = [dbo].[WF_Statistatics_ResourceUseStats]
@startDate = N'2008-02-02',
@endDate = N'2009-04-02',
@resIDs = NULL,
@PageSize = 10,
@PageIndex = 1

SELECT 'Return Value' = @return_value

GO

结果执行的时候报了消息 4104，级别 16，状态 1，过程 WF_Statistatics_ResourceUseStats，第 27 行
无法绑定由多个部分组成的标识符 "r.RS_ID"。
也就是“ SELECT r.RS_ID AS ResID”这一行。找了半天也没看出哪里错了，大家帮我看一下，100不成敬意，谢谢！

[-狙击手-]

ALTER PROCEDURE [dbo].[WF_Statistatics_ResourceUseStats] 
  @startDate datetime, 
  @endDate datetime, 
  @resIDs varchar(8000), 
@PageSize  int = 10,          -- 页尺寸 
@PageIndex  int = 1          -- 页码 
AS 
declare @strSQL1  varchar(4000)      -- 主语句 
DECLARE @PageLowerBound varchar(30) 
DECLARE @PageUpperBound varchar(30) 
SET @PageLowerBound = convert(varchar,@PageSize * @PageIndex) 
SET @PageUpperBound = convert(varchar,@PageSize - 1 + @PageLowerBound) 


CREATE TABLE #PageIndexTable 
    ( 
        IndexId int IDENTITY (0, 1) NOT NULL, 
        IndexField varchar(300) 
    ) 
    
    -- Insert into our temp table 
    set @strSQL1='INSERT INTO #PageIndexTable (IndexField) SELECT rs_id from WF_RS' 
exec(@strSQL1) 
/*统计全部*/ 
IF (@ResIDs IS NULL) 
BEGIN 
SELECT r.RS_ID AS ResID 
,r.RS_Name AS ResName 
,ISNULL(SearchAmount,0) AS SearchAmount 
,ISNULL(TrackAmount,0) AS TrackAmount 
,ISNULL(BrowseAmount,0) AS BrowseAmount 
,ISNULL(DownloadAmount,0) AS DownloadAmount 
,ISNULL(OriRequestAmount,0) AS OriRequestAmount 
FROM WF_RS r
left join  #PageIndexTable p on  1 = 1
LEFT JOIN /*检索次数*/ 
( 
SELECT COUNT(sl.SearchLogID) AS SearchAmount 
, ResID 
FROM WF_SearchLog sl 
INNER JOIN WF_SearchResLog srl ON sl.SearchLogID = srl.SearchLogID 
INNER JOIN WF_SearchRes sr ON srl.SearchResID = sr.SearchResID 
WHERE (sl.SearchDateStamp BETWEEN @startDate AND @endDate) 
GROUP BY ResID 
) s ON r.RS_ID = s.ResID 
LEFT JOIN /*点击量*/ 
( 
SELECT COUNT(RESID) AS TrackAmount 
,ResID 
FROM ResTrackLog 
WHERE (TrackDate BETWEEN @startDate AND @endDate) 
GROUP BY ResID 
) tl ON r.RS_ID = tl.ResID 
LEFT JOIN /*浏览次数*/ 
( 
SELECT COUNT(*) BrowseAmount, RS_ID 
FROM WF_Browse_log 
WHERE Browse_Time BETWEEN @startDate AND @endDate 
GROUP BY RS_ID 
) B ON r.RS_ID = B.RS_ID 
LEFT JOIN /*下载次数*/ 
( 
SELECT COUNT(ResID) DownloadAmount, ResID 
FROM ResDownloadLog 
WHERE DownloadDate BETWEEN @startDate AND @endDate 
GROUP BY ResID 
) D ON r.RS_ID = D.ResID 
LEFT JOIN /*消费金额*/ 
( 
SELECT SUM(OrderMoney) AS OriRequestAmount 
, ResID 
FROM WF_OrderDtl 
GROUP BY ResID 
) O ON r.RS_ID = o.ResID 
where r.RS_ID=p.IndexField and p.IndexId >='+@PageLowerBound+' AND p.IndexId <='+@PageUpperBound' 
ORDER BY ResName 
END 
ELSE 
BEGIN 
SELECT r.RS_ID AS ResID 
,r.RS_Name AS ResName 
,ISNULL(SearchAmount,0) AS SearchAmount 
,ISNULL(TrackAmount,0) AS TrackAmount 
,ISNULL(BrowseAmount,0) AS BrowseAmount 
,ISNULL(DownloadAmount,0) AS DownloadAmount 
,ISNULL(OriRequestAmount,0) AS OriRequestAmount 
FROM WF_RS r left join  #PageIndexTable p on  1 = 1 
INNER JOIN UDF_iter_charlist_to_table(@ResIDs,default) ids ON r.RS_ID = ids.nstr 
LEFT JOIN /*检索次数*/ 
( 
SELECT COUNT(sl.SearchLogID) AS SearchAmount 
, ResID 
FROM WF_SearchLog sl 
INNER JOIN WF_SearchResLog srl ON sl.SearchLogID = srl.SearchLogID 
INNER JOIN WF_SearchRes sr ON srl.SearchResID = sr.SearchResID 
WHERE (sl.SearchDateStamp BETWEEN @startDate AND @endDate) 
GROUP BY ResID 
) s ON r.RS_ID = s.ResID 
LEFT JOIN /*点击量*/ 
( 
SELECT COUNT(RESID) AS TrackAmount 
,ResID 
FROM ResTrackLog 
WHERE (TrackDate BETWEEN @startDate AND @endDate) 
GROUP BY ResID 
) tl ON r.RS_ID = tl.ResID 
LEFT JOIN /*浏览次数*/ 
( 
SELECT COUNT(*) BrowseAmount, RS_ID 
FROM WF_Browse_log 
WHERE Browse_Time BETWEEN @startDate AND @endDate 
GROUP BY RS_ID 
) B ON r.RS_ID = B.RS_ID 
LEFT JOIN /*下载次数*/ 
( 
SELECT COUNT(ResID) DownloadAmount, ResID 
FROM ResDownloadLog 
WHERE DownloadDate BETWEEN @startDate AND @endDate 
GROUP BY ResID 
) D ON r.RS_ID = D.ResID 
LEFT JOIN /*消费金额*/ 
( 
SELECT SUM(OrderMoney) AS OriRequestAmount 
, ResID 
FROM WF_OrderDtl 
GROUP BY ResID 
) O ON r.RS_ID = o.ResID 
WHERE r.State=1 AND r.Enable=1 
ORDER BY ResName 
END 

我在2005中是这样运行的： 
DECLARE @return_value int 

EXEC @return_value = [dbo].[WF_Statistatics_ResourceUseStats] 
@startDate = N'2008-02-02', 
@endDate = N'2009-04-02', 
@resIDs = NULL, 
@PageSize = 10, 
@PageIndex = 1 

SELECT 'Return Value' = @return_value 

GO 

[claro]

帮顶。

[look4u]

happyflystone ，你太厉害了，能跟我说说原理么！

[htl258_Tony]

表别名套错.慢慢看下.

[nzperfect]

仔细检查下列名或列别名重复.

[look4u]

我看了一下，没有发现。。。。。
去掉临时表这部分是可以运行的，但是没有办法翻页

[look4u]

现在报的错误是：有五处，说明是五个地方都是同样的错误
(32 行受影响)
消息 4104，级别 16，状态 1，过程 WF_Statistatics_ResourceUseStats，第 27 行
无法绑定由多个部分组成的标识符 "r.RS_ID"。
消息 4104，级别 16，状态 1，过程 WF_Statistatics_ResourceUseStats，第 27 行
无法绑定由多个部分组成的标识符 "r.RS_ID"。
消息 4104，级别 16，状态 1，过程 WF_Statistatics_ResourceUseStats，第 27 行
无法绑定由多个部分组成的标识符 "r.RS_ID"。
消息 4104，级别 16，状态 1，过程 WF_Statistatics_ResourceUseStats，第 27 行
无法绑定由多个部分组成的标识符 "r.RS_ID"。
消息 4104，级别 16，状态 1，过程 WF_Statistatics_ResourceUseStats，第 27 行
无法绑定由多个部分组成的标识符 "r.RS_ID"。

(1 行受影响)

[等不到来世]

--可以解释下这是什么语法吗？
where r.RS_ID=p.IndexField and p.IndexId >='+@PageLowerBound+' AND p.IndexId <='+@PageUpperBound' 

--为何不写成：
where r.RS_ID=p.IndexField and p.IndexId >=@PageLowerBound AND p.IndexId <=@PageUpperBound

[look4u]

我查了，不过我还有一个疑问，为什么你用了FROM WF_RS r left join #PageIndexTable p on 1 = 1，在结果中确没有把#PageIndexTable 中的所有列都追加到右边呢？

[-狙击手-]

[Quote=引用 8 楼 look4u 的回复:]
happyflystone ，你太厉害了，能跟我说说原理么！
[/Quote]

对了？
原理？

你可以查查联机帮助关于联接这一块
左、右、内联的标准写法

[Zoezs]

一看见，一大串，格式又乱七八糟的头就晕。

[Roc_Lee]

字段重复。应该是多个表中有重名 的吧

[战斗生活]

是不是哪里拼写出错了哦 注意检查哦

[taozhi_yaoyao]

厉害学习了

[zzz1975]

看见这么长东西，就头晕

[zjsomnus]

冗长

[叫我 Teacher 周]

也没给个解释……