这个Sql错在哪呢？
---我定义的存储过程---
use tzhy
declare @pageIndex int,@pageSize int,@pageCount int
set @pageIndex=751
set @pageSize=20
exec usp_GetPagedData @pageIndex,@pageSize,@pageCount output
select @pageCount
--SDataTime, SClasses, SCollector, SCollectorNum, SCarNum, SCarOldType, SCarNewType, SComeStation, SOutStation, SFreeMuch, SMeterMuch
use tzhy
create proc usp_GetPageData2
@pageIndex int, --页码
@pageSize int, --每页多少条
@pageCount int output, --总共多少页 输出
@tableName nvarchar(50), ---表名
@colFields nvarchar(200) ---字段名
as
--总记录数
declare @count int
select @count=COUNT(*) from Table_Meter
set @pageCount = CEILING(@count*1.0/@pageSize)
declare @sql nvarchar(500)
set @sql='select * from
(select *,ROW_NUMBER() over(order by [SID] desc) as num from '+@tableName+')
as t
where t.num between ('+@pageIndex+'-1)*'+@pageSize+'+1 and '+@pageIndex+'*'+@pageSize+' order by [SID] desc'
exec(@sql)
-------------------------------------------------------------
----下面我想对这个写好的存储过程测试，测试如下-----
declare @pageIndex int,@pageSize int,@pageCount int,@tableName nvarchar(50),@colFields nvarchar(200)
set @pageIndex=751
set @pageSize=20
set @tableName='dbo.Table_Meter'
set @colFields='SDataTime, SClasses, SCollector, SCollectorNum, SCarNum, SCarOldType'
exec usp_GetPageData2 @pageIndex,@pageSize,@pageCount output,@tableName,@colFields
----执行上面的测试代码，但报错了，错误如下----
消息 245，级别 16，状态 1，过程 usp_GetPageData2，第 14 行
在将 nvarchar 值 'select * from
(select *,ROW_NUMBER() over(order by [SID] desc) as num from dbo.Table_Meter)
as t
where t.num between (' 转换成数据类型 int 时失败。
---------------请问这错在哪里，我怎么就看不出来呢？？？？-----
[解决办法]
你把@pageIndex转换成字符型试试
CAST(@pageIndex AS CHAR(5))
[解决办法]
set @sql='select * from
(select *,ROW_NUMBER() over(order by [SID] desc) as num from '+@tableName+')
as t
where t.num between ('+@pageIndex+'-1)*'+@pageSize+'+1 and '+@pageIndex+'*'+@pageSize+' order by [SID] desc'
>>>
把这个换成
set @sql='select * from
(select *,ROW_NUMBER() over(order by [SID] desc) as num from '+@tableName+')
as t
where t.num between ('+convert(varchar(10),@pageIndex) + '-1)*' + convert(varchar(10),@pageSize) + '+1 and '
+ convert(varchar(10),@pageIndex) + '*' + convert(varchar(10),@pageSize) + ' order by [SID] desc'
就可以了
