1 /****** Object: StoredProcedure [dbo].[usp_selectbypage] Script Date: 01/14/2020 07:51:42 ******/
2 SET ANSI_NULLS ON
3 GO
4 SET QUOTED_IDENTIFIER ON
5 GO
6 ALTER procedure [dbo].[usp_selectbypage]
7 @tablename varchar(10),
8 @columnname varchar(10),
9 @page varchar(5), ---页数
10 @pagecount varchar(5), --每页行数
11 @recordcount int output,--总的记录的条数
12 @pageamount int output --总页数
13 as
14 begin
15 declare @strsql nvarchar(255)
16
17 set @strsql = 'select * from (
18 select ROW_NUMBER() over (order by ' + @columnname + ' desc) as rowid,* from ' + @tablename + ') t
19 where t.rowid >=(('+@page+'-1)*'+@pagecount+'+1) and t.rowid <= ('+@page+'*'+@pagecount+')'
20
21 exec (@strsql) -----不是有效的标识符 sql,exec执行时必须加（）
22
23 set @strsql = 'select @count=count(*) from ' + @tablename
24
25 exec sp_executesql @strsql,N'@count int output',@recordcount output
26
27 select @pageamount = CEILING(@recordcount*1.0/@pagecount)
28
29 end
