我要用AspNetPager做分页,生成了一个存储过程,但运行时总是说:在将 varchar 值 'SELECT 公司编号,自编号,投产日期,车辆类型 FROM BV_车辆 #indextable t WHERE 公司编号=t.nid AND t.id> ' 转换成数据类型 int 时失败。请教!
ALTER PROCEDURE jlc
(@pagesize int,
@pageindex int,
@docount bit = 0,
@orderName varchar(100), --排序字段
@where varchar(200), --条件语句
@getFieldName varchar(1000), --返回列
@tableName varchar(100), --表名
@countName varchar(100)) --统计字段
AS
declare @sqlStr2 varchar(4000)
BEGIN
CREATE TABLE #indextable(id int identity(1,1),nid int)
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @sqlStr1 Varchar(4000)
SET @PageLowerBound=(@pageindex-1)*@pagesize
SET @PageUpperBound=@PageLowerBound+@pagesize
SET ROWCOUNT @PageUpperBound
SET @sqlStr1='INSERT INTO #indextable(nid) SELECT '+@countName+' FROM '+@tableName+' WHERE ' + @where + ' ORDER BY ' + @orderName
EXEC(@sqlStr1)
--要返回的查询记录值
SET @sqlStr2='SELECT '+@getFieldName+' FROM '+@tableName+' #indextable t WHERE '+@countName+'=t.nid AND t.id> '+@PageLowerBound+' AND t.id<='+@PageUpperBound+' ORDER BY t.id'
exec @sqlStr2
END
RETURN
ALTER PROCEDURE jlc
(@pagesize int,
@pageindex int,
@docount bit = 0,
@orderName varchar(100), --排序字段
@where varchar(200), --条件语句
@getFieldName varchar(1000), --返回列
@tableName varchar(100), --表名
@countName varchar(100)) --统计字段
AS
declare @sqlStr2 varchar(4000)
BEGIN
CREATE TABLE #indextable(id int identity(1,1),nid int)
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @sqlStr1 Varchar(4000)
SET @PageLowerBound=(@pageindex-1)*@pagesize
SET @PageUpperBound=@PageLowerBound+@pagesize
SET ROWCOUNT @PageUpperBound
SET @sqlStr1='INSERT INTO #indextable(nid) SELECT '+@countName+' FROM '+@tableName+' WHERE ' + @where + ' ORDER BY ' + @orderName
EXEC(@sqlStr1)
--要返回的查询记录值
SET @sqlStr2='SELECT '+@getFieldName+' FROM '+@tableName+' #indextable t WHERE '+@countName+'=t.nid AND t.id> '+@PageLowerBound+' AND t.id<='+@PageUpperBound+' ORDER BY t.id'
exec @sqlStr2
END
RETURN
-------------------------------------
类似这样的数值型变量,连接到动态SQL中时,需要先转换为字符型,如:AND t.id<='+ cast(@PageUpperBound as varchar(100)) +'
exec @sqlStr2--> SET @sqlStr2='SELECT '+@getFieldName+' FROM '+@tableName+' #indextable t WHERE '+@countName+'=t.nid AND t.id> '
+ cast(@PageLowerBound as varchar(100)) --转换为字符型
+' AND t.id<='+cast(@PageUpperBound as varchar(100)) --转换为字符型
+' ORDER BY t.id'
exec (@sqlStr2) --括号必须加