轻量级分页存储过程

发一个sql2005,sql2008的通用存储分页过程,适合小型系统用.

本存储是通过sql语句拼接的方式获取分页数据

USE [EquipmentReservation]
GO
/****** Object:  StoredProcedure [dbo].[sp_Page]    Script Date: 08/06/2010 08:24:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_Page]
(@table varchar(50),
@pagesize int,
@pageindex int,
@strWhere varchar(200),
@orderField varchar(50),
@ascField int)
as
declare @sql varchar(1000)
begin
set @sql ='with temptbl as ('
set @sql = @sql + 'SELECT ROW_NUMBER() OVER (ORDER BY '
if @orderField != ''
begin
 set @sql = @sql + @orderField
end
if @ascField =0
 set @sql = @sql + ' ASC '
else
 set @sql = @sql + ' DESC '
set @sql = @sql + ')AS Row, * from ' + @table + ' O '
if @strWhere !=''
begin
 set @sql = @sql + ' where ' + @strWhere
end
set @sql = @sql +')'
set @sql = @sql +' SELECT * FROM temptbl where Row between' + str((@pageindex-1)*@pagesize+1) + 'and' + str((@pageindex-1)*@pagesize+@pagesize)
exec(@sql)
end

 

 

调用方法:

SqlParameter[] parameters = {
                    new SqlParameter("@table", SqlDbType.VarChar, 100),
                    new SqlParameter("@pagesize", SqlDbType.Int),
                    new SqlParameter("@pageindex", SqlDbType.Int),
                    new SqlParameter("@strWhere",SqlDbType.VarChar,200),
                    new SqlParameter("@orderField",SqlDbType.VarChar,50), 
                    new SqlParameter("@ascField",SqlDbType.Int)};
            parameters[0].Value = "Equipment";
            parameters[1].Value = PageSize;
            parameters[2].Value = PageIndex;
            parameters[3].Value = strWhere;
            parameters[4].Value = "ID";
            parameters[5].Value = 0;
            SqlDataReader sdr = DbHelperSQL.RunProcedure("sp_Page", parameters);