1 --分页查询
2 alter proc ShowInfo
3 @Table nvarchar(50),--表.视图
4 @PageSize int,--每页显示数量
5 @PageIndex int,--当前显示页码
6 @Conditions nvarchar(300),--筛选条件
7 @Pages int output--返回总共有多少页
8 as
9 declare @start int ,--当前页开始显示的No
10 @end int,--当前页结束显示的No
11 @Context nvarchar(1024), --动态sql语句
12 @pkey nvarchar(10)--主键或索引
13 set @start=(@PageIndex-1)+1
14 set @end=@start+@PageSize-1
15 set @pkey=index_col(@Table,1,1)--获取主键,或索引
16 --通过条件将符合要求的数据汇聚到临时表#temp上
17 set @Context='select row_number() over(order by '+@pkey+') as [No],* into #temp from '+@Table
18 --判断是否有筛选条件传入
19 if(@Conditions is not null)
20 set @Context=@Context+' where '+@Conditions
21 --通过查询#temp 表实现分页.
22 set @Context=@Context+' select * from #temp where No between '+cast(@start as nvarchar(4))+' and '+cast(@end as nva rchar(4))
23 --返回出总共可以分成多少页
24 set @Context=@Context+' declare @count int select @count=count(*) from #temp set @Pages= @count/'+cast(@PageSi ze as nvarchar(4))+' if(@count%'+cast(@PageSize as nvarchar(4))+'<>0) set @Pages=@Pages+1 '
25
26 exec sp_executesql @Context,N'@Pages int output', @Pages output
27 -- sp_executesql @动态sql语句,@动态sql语句中需要的参数,@传入动态sql语句参数的值(个人理解)
28 ---------------------------------------------------------------------------------------------------
29 declare @p int
30 exec ShowInfo 'Products',10,1,null, @p output
31 select @p