经常用到的一个分页存储过程
.net分页调用时与AspNetPager结合很好用的。
1
CREATE procedure Common_PageList(
2
@select_list varchar(1000)='*',--不需要select
3
@table_name varchar(1000),
4
@where varchar(1000)='',--不需要where
5
@primary_key varchar(100),--当是表联合时,加表名前缀.
6
@order_by varchar(200),--需要完整的子句
7
@page_size smallint=20,--每页记录
8
@page_index int=1,--页索引
9
@bl_page int=1)--0 取所有记录集
10
as
11![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
declare @sql_str varchar(8000)
14
declare @record_min int
15
declare @new_where varchar(1000),@newin_where varchar(1000)
16
if @where=''--重新为梳理,此过程时性能的考虑,因此不使用 where 1=1 再追加条件。
17
begin
18
select @new_where=''
19
select @newin_where=''
20
end
21
else
22
begin
23
select @new_where=' and '+@where
24
select @newin_where=' where '+@where
25
end
26![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
if @order_by!=''
28
begin
29
select @order_by=' order by '+@order_by
30
end
31![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
if @bl_page=0
33
select @sql_str='select '+@select_list +' from '+@table_name+' '+@newin_where+' '+@order_by
34
else
35
if @page_index=1
36
if @where=''
37
select @sql_str='select top '+convert(varchar,@page_size)+ ' '+@select_list+' from '+@table_name+' '+@order_by
38
else
39
select @sql_str='select top '+convert(varchar,@page_size)+ ' '+@select_list+' from '+@table_name+' where '+@where+' '+@order_by
40
else
41
begin
42
select @record_min=(@page_index-1)*@page_size
43
select @sql_str='select top '+convert(varchar,@page_size)+' '+@select_list+' from '+@table_name+' where '+@primary_key+' not in '
44
select @sql_str=@sql_str+' (select top '+convert(varchar,@record_min)+' '+@primary_key+' from '+@table_name+@newin_where+' '+@order_by+')'
45
select @sql_str=@sql_str+@new_where+' '+@order_by
46
end
47
print @sql_str
48
exec(@sql_str)
49![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
50![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
51
select @sql_str='select 1 from ' +@table_name+' '+@newin_where
52
exec(@sql_str)
53![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
54
return @@rowcount
55![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
56![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
57![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
46
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
47
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
48
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
49
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
50
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
51
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
52
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
53
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
54
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
55
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
56
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
57
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)