MS-SQL 分页函数+ASP

核心提示：CREATE PROCEDURE [dbo].[USP_GetList] ( @pageIndex int = 1 -- 当前页码0 表示返回所有符合内容 ,@pageSize int = 10 -- 页尺寸 ,@SID nvarchar(30) = '' -- 主键字段 ,@strGetField n

CREATE PROCEDURE [dbo].[USP_GetList]

(

@pageIndex int = 1 -- 当前页码0 表示返回所有符合内容

,@pageSize int = 10 -- 页尺寸

,@SID nvarchar(30) = '' -- 主键字段

,@strGetField nvarchar(1000) = '*' -- 需要返回的列

,@strTableName nvarchar(30) = '' --表名

,@strWhere nvarchar(2000) = '' -- 查询条件(注意: 条件中要带where)

,@strOrderBy nvarchar(300) = '' -- 排序

--,@MemberID int=0 --会员ID

)

AS

SET NOCOUNT ON

DECLARE @strSQL nvarchar(4000)

DECLARE @startPos int

DECLARE @endPos int

DECLARE @num int

SET @startPos=@pageSize*(@pageIndex-1)+1

SET @endPos=@startPos+@pageSize-1

--页大小*(页数-1)

SET @num = @pageSize * (@PageIndex - 1)

IF @PageIndex!=0

BEGIN

IF @strWhere != ''

SET @strSQL = 'SELECT TOP '+ cast(@pageSize as nvarchar(10)) +' '+ @strGetField +' FROM '+ @strTableName +' WHERE ('+ cast(@SID as nvarchar(30)) +' NOT IN (SELECT TOP '+ cast(@num as nvarchar(20)) +' '+ cast(@SID as nvarchar(30)) +' FROM '+ @strTableName +' where '+@strWhere+' ORDER BY '+ cast(@strOrderBy as nvarchar(255)) +' )) and '+@strwhere+' ORDER BY '+cast(@strOrderBy as varchar(255)) + ''

else

SET @strSQL = 'SELECT TOP '+ cast(@pageSize as nvarchar(10)) +' '+ @strGetField +' FROM '+ @strTableName +' WHERE ('+ cast(@SID as nvarchar(30)) +' NOT IN (SELECT TOP '+ cast(@num as nvarchar(20)) +' '+ cast(@SID as nvarchar(30)) +' FROM '+ @strTableName +' ORDER BY '+ cast(@strOrderBy as nvarchar(255)) +' )) ORDER BY '+ cast(@strOrderBy as varchar(255)) + ''