求解 无法绑定由多个部分组成的标识符？？？

[derek1321]

我有个存储过程，传参进入就报错。
但是我直接查询语句是可以执行的。求解啊？

分页的，我Where条件传
t_TaskDetail.DataMark in(1,4,6) and t_ReturnVisit.CreateTime >='2013-05-01' and t_ReturnVisit.CreateTime <'2013-05-11 23:59:59' 就报无法绑定由多个部分组成的标识符[t_ReturnVisit.CreateTime]的错，
但是我直接执行
SELECT t_TaskDetail.ID AS ID,
t_TaskDetail.TaskEmpNo AS TaskEmpNo,
t_TaskDetail.Planner AS Planner,
t_TaskDetail.IsReturn AS IsReturn,
t_TaskDetail.EndTime AS EndTime,
t_ErrorDetail.Title AS Title,
t_ErrorDetail.CreateTime AS BeginTime,
t_ErrorDetail.Declarer AS Declarer,
t_ErrorDetail.OrgNo AS OrgNo,
t_ErrorDetail.TelNum AS TelNum,
t_ErrorDetail.MobileNum AS MobileNum,
t_ErrorDetail.Location AS Location,
t_ReturnVisit.CreateTime AS ReturnTime
FROM t_ReturnVisit right join t_ErrorDetail INNER JOIN
t_TaskDetail ON t_ErrorDetail.ID = t_TaskDetail.ErrorID on
(dbo.t_TaskDetail.ID = dbo.t_ReturnVisit.TaskID)
where t_TaskDetail.DataMark in(1,4,6) and t_ReturnVisit.CreateTime >='2013-05-01' and t_ReturnVisit.CreateTime <'2013-05-11 23:59:59'
是可以执行的，

存储过程如下：
ALTER PROCEDURE [dbo].[p_TaskReturn_List2]
@FieldsClause VARCHAR(2000) = NULL, --查询列子句
@WhereClause VARCHAR(2000) = NULL, --查询条件子句
@OrderClause VARCHAR(2000) = NULL, --查询排序子句
@PageSize INT = 0, -- 分页查询的每页数量，为0表示查询全部，不分页。
@PageNumber INT = 1, -- 分页查询的当前页号
@TotalSize INT OUTPUT, -- 返回值，查询结果记录条数
@TotalPage INT OUTPUT -- 返回值，查询结果总页数
AS
BEGIN
SET NOCOUNT ON
-- 声明变量
DECLARE @Sql NVARCHAR(4000) -- 用于保存动态组织的sql语句
DECLARE @ParmDefinition NVARCHAR(500) -- 用于保存临时参数
DECLARE @MinRowIndex INT -- 分页查询的最小行号
DECLARE @MaxRowIndex INT -- 分页查询的最大行号

-- 检查参数合法性，并设置为可用值
SET @FieldsClause = LTRIM(RTRIM(ISNULL(@FieldsClause,'*'))) -- 缺省查询所有字段
SET @WhereClause = LTRIM(RTRIM(ISNULL(@WhereClause,'')))
SET @OrderClause = LTRIM(RTRIM(ISNULL(@OrderClause,'')))
IF(@OrderClause = '') SET @OrderClause = '[ID]'
IF(@PageSize < 0) SET @PageSize = 0
IF(@PageNumber < 1) SET @PageNumber = 1

IF(@PageSize = 0) -- 为0表示不分页
BEGIN
-- 如果不分页
-- 组织查询语句
SET @Sql = N'
SELECT t_TaskDetail.ID AS ID,
t_TaskDetail.TaskEmpNo AS TaskEmpNo,
t_TaskDetail.Planner AS Planner,
t_TaskDetail.IsReturn AS IsReturn,
t_TaskDetail.EndTime AS EndTime,
t_ErrorDetail.Title AS Title,
t_ErrorDetail.CreateTime AS BeginTime,
t_ErrorDetail.Declarer AS Declarer,
t_ErrorDetail.OrgNo AS OrgNo,
t_ErrorDetail.TelNum AS TelNum,
t_ErrorDetail.MobileNum AS MobileNum,
t_ErrorDetail.Location AS Location,
t_ReturnVisit.CreateTime AS ReturnTime
FROM t_ReturnVisit right join t_ErrorDetail INNER JOIN
t_TaskDetail ON t_ErrorDetail.ID = t_TaskDetail.ErrorID on
(dbo.t_TaskDetail.ID = dbo.t_ReturnVisit.TaskID)'

-- 附加WHERE子句
IF(@WhereClause <> '')
BEGIN
SET @Sql = @Sql + N'
WHERE ' + @WhereClause
END

-- 附加ORDER子句
IF(@OrderClause <> '')
BEGIN
SET @Sql = @Sql + N'
ORDER BY ' + @OrderClause
END

-- 执行语句
--PRINT @Sql
EXEC(@Sql)

-- 设置返回值
SET @TotalSize = @@ROWCOUNT
SET @TotalPage = 1
END
ELSE
BEGIN
-- 如果分页

-- 检查：排序语句不能为空，建议至少使用主键做排序
IF(@OrderClause = '')
BEGIN
RAISERROR(N'请务必给参数@OrderClause输入有效的排序语句',15,1)
RETURN
END

-- 计算总条数
SET @Sql = N'
SELECT @RowCountOut=COUNT(1) FROM [dbo].[t_TaskDetail]'

IF(@WhereClause <> '')
BEGIN
SET @Sql = @Sql + N'
WHERE ' + @WhereClause
END

SET @ParmDefinition = N'
@RowCountOut INT OUTPUT';

EXECUTE sp_executesql @Sql,@ParmDefinition,@RowCountOut = @TotalSize OUTPUT
-- PRINT @TotalSize

-- 根据总记录条数，计算总页数
SET @TotalPage = CEILING(CAST(@TotalSize AS NUMERIC(10,2)) / @PageSize)

-- 根据计算总页数，更正查询页数
IF(@PageNumber > @TotalPage) SET @PageNumber = @TotalPage

-- 计算最小、最大行号
SET @MinRowIndex = (@PageNumber - 1) * @PageSize + 1
SET @MaxRowIndex = @MinRowIndex + @PageSize - 1


-- 组织查询语句
SET @Sql = N'
SELECT ID,
TaskEmpNo,
Planner,
IsReturn,
EndTime,
Title,
BeginTime,
Declarer,
OrgNo,
TelNum,
MobileNum,
ReturnTime,
Location FROM (
SELECT TOP ' + CAST(@MaxRowIndex AS NVARCHAR(10)) + ' t_TaskDetail.ID AS ID,
t_TaskDetail.TaskEmpNo AS TaskEmpNo,
t_TaskDetail.Planner AS Planner,
t_TaskDetail.IsReturn AS IsReturn,
t_TaskDetail.EndTime AS EndTime,
t_ErrorDetail.Title AS Title,
t_ErrorDetail.CreateTime AS BeginTime,
t_ErrorDetail.Declarer AS Declarer,
t_ErrorDetail.OrgNo AS OrgNo,
t_ErrorDetail.TelNum AS TelNum,
t_ErrorDetail.MobileNum AS MobileNum,
t_ErrorDetail.Location AS Location ,
t_ReturnVisit.CreateTime AS ReturnTime, '
SET @Sql = @Sql + N'
ROW_NUMBER() OVER(ORDER BY EndTime Desc,
t_ErrorDetail.CreateTime,
t_TaskDetail.ID,
TaskEmpNo,
Planner,
IsReturn,
Title,
Declarer,
OrgNo,
TelNum,
MobileNum,
t_ReturnVisit.CreateTime,
Location) AS ''RowNo'''
SET @Sql = @Sql + N'
FROM t_ReturnVisit right join t_ErrorDetail INNER JOIN
t_TaskDetail ON t_ErrorDetail.ID = t_TaskDetail.ErrorID on
(dbo.t_TaskDetail.ID = dbo.t_ReturnVisit.TaskID)'

-- 附加WHERE子句
IF(@WhereClause <> '')
BEGIN
SET @Sql = @Sql + N'
WHERE ' + @WhereClause
END

-- 附加行号范围子句
SET @Sql = @Sql + '
) tmp WHERE RowNo BETWEEN ' + CAST(@MinRowIndex AS nvarchar(10)) + ' AND ' + CAST(@MaxRowIndex AS nvarchar(10))

-- 附加ORDER子句
IF(@OrderClause <> '')
BEGIN
SET @Sql = @Sql + N'
ORDER BY ' + @OrderClause
END
-- 执行语句
--PRINT @Sql
EXEC(@Sql)
END
END

有点长，求大神来解答啊~~

[發糞塗牆]

有个地方两个表或者多个表有相同的字段但是你没加上别名去区分，那么长看不出来，你自己调试一下吧

[發糞塗牆]

主要在select那里

[derek1321]

引用 2 楼 DBA_Huangzj 的回复:

主要在select那里

SELECT TOP ' + CAST(@MaxRowIndex AS NVARCHAR(10)) + ' t_TaskDetail.ID AS ID, t_TaskDetail.TaskEmpNo AS TaskEmpNo, t_TaskDetail.Planner AS Planner, t_TaskDetail.IsReturn AS IsReturn, t_TaskDetail.EndTime AS EndTime, t_ErrorDetail.Title AS Title, t_ErrorDetail.CreateTime AS BeginTime, t_ErrorDetail.Declarer AS Declarer, t_ErrorDetail.OrgNo AS OrgNo, t_ErrorDetail.TelNum AS TelNum, t_ErrorDetail.MobileNum AS MobileNum, t_ErrorDetail.Location AS Location , t_ReturnVisit.CreateTime AS ReturnTime, ' SET @Sql = @Sql + N' ROW_NUMBER() OVER(ORDER BY EndTime Desc, t_ErrorDetail.CreateTime, t_TaskDetail.ID, TaskEmpNo, Planner, IsReturn, Title, Declarer, OrgNo, TelNum, MobileNum, t_ReturnVisit.CreateTime, Location) AS ''RowNo''' SET @Sql = @Sql + N' FROM t_ReturnVisit right join t_ErrorDetail INNER JOIN t_TaskDetail ON t_ErrorDetail.ID = t_TaskDetail.ErrorID on (dbo.t_TaskDetail.ID = dbo.t_ReturnVisit.TaskID)

[發糞塗牆]

有可能是row_number里面那些，你试试先print出来再执行

[derek1321]

引用 4 楼 DBA_Huangzj 的回复:

有可能是row_number里面那些，你试试先print出来再执行

这样执行是没错的， 我加上Where条件就报错了。好郁闷啊~

[發糞塗牆]

加了where之后print出来呢？

[derek1321]

引用 6 楼 DBA_Huangzj 的回复:

加了where之后print出来呢？

哦，那天后来PRINT了，已经成功了，谢谢你了哈！~