通用的分页存储过程，很久以前写的
1 CREATE procedure [dbo].[CommonPageSelect]
2 (
3 @SqlTable varchar(5000),--要查询的表或视图,也可以一句sql语句
4 @SqlColumn varchar(4000),--查询的字段
5 @SqlWhere varchar(800)='', --查询条件
6 @pagenum int=20,--每页的记录数
7 @beginline int=1, --第几页,默认第一页
8 @SqlPK varchar(100),--主键
9 @SqlOrder Varchar(100),
10 @Count int=0 output
11 )
12 as
13 set nocount on
14 declare @PageLowerBound int
15 declare @PageUpperBound int
16 declare @sqlstr nvarchar(4000)
17
18
19
20 --获取记录数
21 IF @beginline=1 --可根据实际要求修改条件,如果是总是获取记录数
22 BEGIN
23 set @sqlstr=N'select @sCount=count(1) FROM '+@SqlTable
24
25 if @SqlWhere !=''
26 begin
27 set @sqlstr= @sqlstr +' where 1=1 '+@SqlWhere
28 end
29 print @sqlstr
30 Exec sp_executesql @sqlstr,N'@sCount int outPut',@Count output
31
32 if(CHARINDEX('Distinct ',@SqlColumn)>=0)
33 begin
34 set @sqlstr= 'select Distinct top '+Convert(varchar(10),@pagenum)+' '+REPLACE(@SqlColumn,'Distinct ',' ')+' from '+@SqlTable
35 end
36 else
37 begin
38 set @sqlstr= 'select top '+Convert(varchar(10),@pagenum)+' '+@SqlColumn+' from '+@SqlTable
39 end
40 if @SqlWhere !=''
41 begin
42 set @sqlstr= @sqlstr +' where 1=1 '+@SqlWhere
43 end
44 if @SqlOrder !=''
45 begin
46 set @sqlstr= @sqlstr +' '+@SqlOrder
47 end
48 print @sqlstr
49 Exec sp_executesql @sqlstr
50 select @Count
51 return
52 END
53 else
54 begin
55 select @Count =0
56 end
57
58 print @Count
59 /**/
60 set @PageLowerBound=(@beginline-1)*@pagenum
61 set @PageUpperBound=@PageLowerBound+@pagenum
62
63 create table #pageindex(rowcountid int identity(1,1) not null,pageindexpkid int)
64 set rowcount @PageUpperBound
65 set @sqlstr=N'insert into #pageindex(pageindexpkid) select '+@SqlPK+' from '+@SqlTable
66 if @SqlWhere !=''
67 begin
68 set @sqlstr= @sqlstr +' where 1=1 '+@SqlWhere
69 end
70 if @SqlOrder !=''
71 begin
72 set @sqlstr= @sqlstr +' '+@SqlOrder
73 end
74
75 print @sqlstr
76 Exec sp_executesql @sqlstr
77
78 set @sqlstr='select '+@SqlColumn+',p.rowcountid FROM '+ @SqlTable +' inner join #pageindex p on '+@SqlPK+'=p.pageindexpkid and (p.rowcountid>'+Convert(varchar(15),@PageLowerBound)+') and (p.rowcountid<='+Convert(varchar(15),@PageUpperBound)+')'
79 print @sqlstr
80 Exec sp_executesql @sqlstr
81
82
83 set nocount off
84 drop table #pageindex
85
86
87 GO
88
2 (
3 @SqlTable varchar(5000),--要查询的表或视图,也可以一句sql语句
4 @SqlColumn varchar(4000),--查询的字段
5 @SqlWhere varchar(800)='', --查询条件
6 @pagenum int=20,--每页的记录数
7 @beginline int=1, --第几页,默认第一页
8 @SqlPK varchar(100),--主键
9 @SqlOrder Varchar(100),
10 @Count int=0 output
11 )
12 as
13 set nocount on
14 declare @PageLowerBound int
15 declare @PageUpperBound int
16 declare @sqlstr nvarchar(4000)
17
18
19
20 --获取记录数
21 IF @beginline=1 --可根据实际要求修改条件,如果是总是获取记录数
22 BEGIN
23 set @sqlstr=N'select @sCount=count(1) FROM '+@SqlTable
24
25 if @SqlWhere !=''
26 begin
27 set @sqlstr= @sqlstr +' where 1=1 '+@SqlWhere
28 end
29 print @sqlstr
30 Exec sp_executesql @sqlstr,N'@sCount int outPut',@Count output
31
32 if(CHARINDEX('Distinct ',@SqlColumn)>=0)
33 begin
34 set @sqlstr= 'select Distinct top '+Convert(varchar(10),@pagenum)+' '+REPLACE(@SqlColumn,'Distinct ',' ')+' from '+@SqlTable
35 end
36 else
37 begin
38 set @sqlstr= 'select top '+Convert(varchar(10),@pagenum)+' '+@SqlColumn+' from '+@SqlTable
39 end
40 if @SqlWhere !=''
41 begin
42 set @sqlstr= @sqlstr +' where 1=1 '+@SqlWhere
43 end
44 if @SqlOrder !=''
45 begin
46 set @sqlstr= @sqlstr +' '+@SqlOrder
47 end
48 print @sqlstr
49 Exec sp_executesql @sqlstr
50 select @Count
51 return
52 END
53 else
54 begin
55 select @Count =0
56 end
57
58 print @Count
59 /**/
60 set @PageLowerBound=(@beginline-1)*@pagenum
61 set @PageUpperBound=@PageLowerBound+@pagenum
62
63 create table #pageindex(rowcountid int identity(1,1) not null,pageindexpkid int)
64 set rowcount @PageUpperBound
65 set @sqlstr=N'insert into #pageindex(pageindexpkid) select '+@SqlPK+' from '+@SqlTable
66 if @SqlWhere !=''
67 begin
68 set @sqlstr= @sqlstr +' where 1=1 '+@SqlWhere
69 end
70 if @SqlOrder !=''
71 begin
72 set @sqlstr= @sqlstr +' '+@SqlOrder
73 end
74
75 print @sqlstr
76 Exec sp_executesql @sqlstr
77
78 set @sqlstr='select '+@SqlColumn+',p.rowcountid FROM '+ @SqlTable +' inner join #pageindex p on '+@SqlPK+'=p.pageindexpkid and (p.rowcountid>'+Convert(varchar(15),@PageLowerBound)+') and (p.rowcountid<='+Convert(varchar(15),@PageUpperBound)+')'
79 print @sqlstr
80 Exec sp_executesql @sqlstr
81
82
83 set nocount off
84 drop table #pageindex
85
86
87 GO
88
