怪问题 sql存储过程中最后参数传入的值为什么被截断

if object_id(N'proc_GetPRO_EVENT_LOGs_wt') is not null
drop proc proc_GetPRO_EVENT_LOGs_wt
go
create proc proc_GetPRO_EVENT_LOGs_wt
@pageIndex int,
@pageSize int,
@begDate datetime,
@endDate datetime,
@searchKey varchar(100)='',
@deptID varchar(50)='',
@orderFieldMain varchar(50)='empID',--主要的排序列
@orderFieldSecondary varchar(50)='theDateTime',--次要的排序列
@rowCount int output
as
declare @totalSize int 
set @totalSize = @pageIndex*@pageSize

select @rowCount = count(*) from view_wtWorkdetails

if (@rowCount < @totalSize)
set @pageSize = @rowCount-((@pageIndex-1)*@pageSize)
declare @sql varchar(900)
set @sql='select * from 
(
select top '+convert(varchar(10),@pageSize)+' * from
(
select top '+convert(varchar(10),@totalSize)+' * from view_PRO_EVENT_LOG
where (theDateTime between '''+convert(varchar(20),@begDate,120)+''' and '''+convert(varchar(20),@endDate,120)+''')
and (charindex('''+@searchKey+''',searchKey)>0 or len('''+@searchKey+''')=0)
and (charindex('''+@deptID+''',deptID)=1 or len('''+@deptID+''')=0)
order by '+@orderFieldMain+' 
)n1 order by '+@orderFieldMain+' desc
)n2 order by '+@orderFieldMain+','+@orderFieldSecondary
print '最后形成的sql:'+@sql
print '最后一个参数:'+@orderFieldSecondary
print 'sql文本长度:'+convert(varchar,len(@sql))
exec (@sql)
go

--测试
declare @pageIndex int ,@pageSize int ,@row int
declare @deptID varchar(50),@name Nvarchar(50),@empID varchar(50),@orderFieldMain varchar(10),@orderFieldSecondary varchar(10)
declare @begDate datetime, @endDate datetime
set @pageIndex=1
set @pageSize=3
set @begDate='2011-11-01'
set @endDate='2011-11-24 23:59:59'
set @deptID=''
set @name =''
set @orderFieldMain='empID'
set @orderFieldSecondary='theDateTime'
exec proc_GetPRO_EVENT_LOGs_wt @pageIndex,@pageSize,@begDate,@endDate,@name,@deptID,@orderFieldMain,@orderFieldSecondary,@row output
print @row

--报错提示
最后形成的sql:select * from 
(
select top 3 * from
(
select top 3 * from view_PRO_EVENT_LOG
where (theDateTime between '2011-11-01 00:00:00' and '2011-11-24 23:59:59')
and (charindex('',searchKey)>0 or len('')=0)
and (charindex('',deptID)=1 or len('')=0)
order by empID 
)n1 order by empID desc
)n2 order by empID,theDateTim
最后一个参数:theDateTim
sql文本长度:346
消息 207，级别 16，状态 1，第 11 行
列名 'theDateTim' 无效。
22

declare @sql varchar(8000)

--這樣試試

被截断说明字符串长度不够，
列名 'theDateTim' 无效。请确定'theDateTim' 列被外表搜索出来

提示很明显了。

字符串长度不够 用

declare @sql varchar(max)