分页存储过程
分页存储过程
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 set ANSI_NULLS ON
2 set QUOTED_IDENTIFIER ON
3 go
4
5 Create PROCEDURE [dbo].[ShowPage]
6 @tblName varchar(255), -- 表名
7 @strGetFields varchar(1000) = ''*'', -- 需要返回的列
8 @strOrder varchar(255)='''', -- 排序的字段名
9 @PageSize int = 10, -- 页尺寸
10 @PageIndex int = 1, -- 页码
11 @strWhere varchar(1500) = '''' -- 查询条件 (注意: 不要加 where)
12 AS
13
14 declare @strSQL varchar(5000)
15
16 if @strWhere !=''''
17 set @strWhere='' where ''+@strWhere
18
19 set @strSQL=''Select * FROM (Select ROW_NUMBER() OVER (''+@strOrder+'') AS pos,''+@strGetFields+'' FROM [''+@tblName+'']''+@strWhere+'') AS sp Where pos BETWEEN ''+str((@PageIndex-1)*@PageSize+1)+'' AND ''+str(@PageIndex*@PageSize)
20
21 exec (@strSQL)
22
2 set QUOTED_IDENTIFIER ON
3 go
4
5 Create PROCEDURE [dbo].[ShowPage]
6 @tblName varchar(255), -- 表名
7 @strGetFields varchar(1000) = ''*'', -- 需要返回的列
8 @strOrder varchar(255)='''', -- 排序的字段名
9 @PageSize int = 10, -- 页尺寸
10 @PageIndex int = 1, -- 页码
11 @strWhere varchar(1500) = '''' -- 查询条件 (注意: 不要加 where)
12 AS
13
14 declare @strSQL varchar(5000)
15
16 if @strWhere !=''''
17 set @strWhere='' where ''+@strWhere
18
19 set @strSQL=''Select * FROM (Select ROW_NUMBER() OVER (''+@strOrder+'') AS pos,''+@strGetFields+'' FROM [''+@tblName+'']''+@strWhere+'') AS sp Where pos BETWEEN ''+str((@PageIndex-1)*@PageSize+1)+'' AND ''+str(@PageIndex*@PageSize)
20
21 exec (@strSQL)
22