基于查询的分页存储过程
基于查询的分页存储过程
1 SET ANSI_NULLS ON
2 GO
3 SET QUOTED_IDENTIFIER ON
4 GO
5
6 CREATE proc [dbo].[queryPage]
7 @tbname nvarchar(20),
8 @getfldname nvarchar(50),
9 @sfldname nvarchar(20),
10 @keyword nvarchar(100),
11 @primary varchar(20),
12 @order int, --0根据@primary升序，1根据primary降序
13 @pageindex int,
14 @pagesize int,
15 @count int output
16 as
17 declare @cttemp Nvarchar(500),@temp Nvarchar(1000),@ordertype Nvarchar(500),@mytemp Nvarchar(200)
18 set @cttemp=N'select @count=count(*) from '+@tbname+' where charindex(@keyword , '+@sfldname+')>0 '
19 execute sp_executesql @cttemp,N'@count int output,@keyword Nvarchar(100)',@count output,@keyword
20 if(@order=0)
21 begin
22 set @ordertype=' order by '+@primary+' asc '
23 set @mytemp=' > (select max('
24 end
25 else if(@order=1)
26 begin
27 set @ordertype=' order by '+@primary+' desc '
28 set @mytemp='< (select min('
29 end
30 if(@pageindex=1)
31 set @temp=N' select top '+str(@pagesize)+' '+@getfldname+' from '+@tbname +' where charindex(@keyfld , '+@sfldname+')>0'+@ordertype
32 else if(@pageindex>1)
33 set @temp=N' select top '+str(@pagesize)+' '+@getfldname+' from '+@tbname+' where '+@primary+@mytemp+@primary+') from ( select top '+str((@pageindex-1)*@pagesize)+' '+@primary+' from '+@tbname+' where charindex(@keyfld , ' +@sfldname+')>0 '+@ordertype+ ') as t) and charindex(@keyfld , '+@sfldname+')>0'+@ordertype
34 exec sp_executesql @temp,N'@keyfld Nvarchar(100)',@keyword
35 GO
36
37 SET ANSI_NULLS OFF
38 GO
39 SET QUOTED_IDENTIFIER OFF
40 GO
