sqlserver 2005存储过程分页的问题

yezisnow 发布于 2012/07/24 17:40
阅读 244
收藏 0
--存储过程
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[UP_GetRecordByPage]
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列 
@fldName varchar(255)='', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere nvarchar(1000) = '' -- 查询条件 (注意: 不要加 where)

AS

declare @strSQL nvarchar(4000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型

if @IsReCount != 0 --如果@IsReCount传递过来的不是0,就执行总数统计
begin
if @strWhere !=''
set @strSQL = 'SELECT COUNT(*) AS Total FROM ' + @tblName + ' WHERE ' + @strWhere
else
set @strSQL = 'SELECT COUNT(*) AS Total FROM ' + @tblName
end 

else

begin

if @OrderType != 0

begin
set @strTmp = '<(SELECT MIN'
set @strOrder = ' ORDER BY [' + @fldName +'] DESC'

--如果@OrderType不是0,就执行降序
end

else

begin

set @strTmp = '>(SELECT MAX'
set @strOrder = ' ORDER BY [' + @fldName +'] ASC'

end

if @PageIndex = 1

begin

if @strWhere != '' 
set @strSQL = 'SELECT TOP ' + str(@PageSize) +' '+@strGetFields+ ' FROM [' + @tblName + '] WHERE ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'SELECT TOP ' + str(@PageSize) +' '+@strGetFields+ ' FROM ['+ @tblName + '] '+ @strOrder

--如果是第一页就执行以上代码,这样会加快执行速度
end

else

begin

--以下代码赋予了@strSQL以真正执行的SQL代码

set @strSQL = 'SELECT TOP ' + str(@PageSize) +' '+@strGetFields+ ' FROM ['
+ @tblName + '] WHERE [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) FROM (SELECT TOP ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] FROM [' + @tblName + ']' + @strOrder + ') AS tblTmp)'+ @strOrder

if @strWhere != ''
set @strSQL = 'SELECT TOP ' + str(@PageSize) +' '+@strGetFields+ ' FROM ['
+ @tblName + '] WHERE [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) FROM (SELECT TOP ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] FROM [' + @tblName + '] WHERE ' + @strWhere + ' '
+ @strOrder + ') AS tblTmp) AND ' + @strWhere + ' ' + @strOrder
end 

end 

exec (@strSQL)

--asp.net读取数据public DataSet GetList(int PageSize,int PageIndex,string strWhere,string ziduan)
{
SqlParameter[] parameters = {
new SqlParameter("@tblName", SqlDbType.VarChar, 255),
new SqlParameter("@fldName", SqlDbType.VarChar, 255),
new SqlParameter("@PageSize", SqlDbType.Int),
new SqlParameter("@PageIndex", SqlDbType.Int),
new SqlParameter("@IsReCount", SqlDbType.Bit),
new SqlParameter("@OrderType", SqlDbType.Bit),
new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
};
parameters[0].Value = "azj_zxgsinfo";
  parameters[1].Value = ziduan;//snow [2012-7-20 10:21:51]
parameters[2].Value = PageSize;
parameters[3].Value = PageIndex;
parameters[4].Value = 0;
parameters[5].Value = 1;
parameters[6].Value = strWhere;
  return DbHelperSQL.RunProcedure("UP_GetRecordByPage", parameters, "ds");
} 
条件是根据会员等级来查询,分页字段不是id而是会员等级字段grade
当分页点击第二页Repeater控件无值显示
加载中
返回顶部
顶部