共需要两个存储过程,第一个是负责分页的
- 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 a