三种SQL分页查询的存储过程代码

--根据MAX(MIN)ID
CREATE PROC [dbo].[proc_select_id]
@pageindex int=1,--当前页数
@pagesize int=10,--每页大小
@tablename VARCHAR(50)=,--表名
@fields VARCHAR(1000)=,--查询的字段集合
@keyid VARCHAR(50)=,--主键
@condition NVARCHAR(1000)=,--查询条件
@orderstr VARCHAR(500),--排序条件
@totalRecord BIGINT OUTPUT--总记录数
AS
IF ISNULL(@orderstr,N)=N SET @orderstr=N ORDER BY +@keyid+N DESC
IF ISNULL(@fields,N)=N SET @fields=N*
IF ISNULL(@condition,N)=N SET @condition=N1=1
DECLARE @sql NVARCHAR(4000)
--IF(@totalRecord IS NULL)
--BEGIN
SET @sql=NSELECT @totalRecord=COUNT(*)
+N FROM +@tablename
+N WHERE +@condition
EXEC sp_executesql @sql,N@totalRecord INT OUTPUT,@totalRecord OUTPUT
--END
IF(@pageindex=1)
BEGIN
SET @sql=NSELECT TOP +STR(@pagesize)+N +@fields+N FROM +@tablename+N WHERE +@condition+N +@orderstr
EXEC(@sql)
END
ELSE
BEGIN
DECLARE @operatestr CHAR(3),@comparestr CHAR(1)
SET @operatestr=MAX
SET @comparestr=>
IF(@orderstr<>)
BEGIN
IF(CHARINDEX(desc,LOWER(@orderstr))<>0)
BEGIN
SET @operatestr=MIN
SET @comparestr=<
END
END
SET @sql=NSELECT top +STR(@pagesize)+N +@fields+N FROM +@tablename+N WHERE +@keyid+@comparestr
+N(SELECT +@operatestr+N(+@keyid+N) FROM +@tablename+N WHERE +@keyid
+N IN (SELECT TOP +STR((@pageindex-1)*@pagesize)+N +@keyid+N FROM +@tablename+N WHERE
+@condition+N +@orderstr+N)) AND +@condition+N +@orderstr
EXEC(@sql)
END
GO


--根据ROW_NUMBER() OVER
CREATE PROC [dbo].[proc_select_page_row]
@pageindex INT=1,--当前页数
@pagesize INT=10,--每页大小
@tablename VARCHAR(50)=,--表名
@fields VARCHAR(1000)=*,--查询的字段集合
@keyid VARCHAR(50)=,--主键
@condition NVARCHAR(1000)=,--查询条件
@orderstr VARCHAR(500),--排序条件
@totalRecord BIGINT OUTPUT--总记录数
AS
IF ISNULL(@orderstr,N)=N SET @orderstr=N ORDER BY +@keyid+N DESC
IF ISNULL(@fields,N)=N SET @fields=N*
IF ISNULL(@condition,N)=N SET @condition=N1=1
DECLARE @sql NVARCHAR(4000)
-- IF @totalRecord IS NULL
-- BEGIN
SET @sql=NSELECT @totalRecord=COUNT(*)
+N FROM +@tablename
+N WHERE +@condition
EXEC sp_executesql @sql,N@totalRecord bigint OUTPUT,@totalRecord OUTPUT
--END
IF(@pageindex=1)
BEGIN
SET @sql=NSELECT TOP +STR(@pagesize)+N +@fields+N FROM +@tablename+N WHERE +@condition+N +@orderstr
EXEC(@sql)
END
ELSE
BEGIN
DECLARE @StartRecord INT
SET @StartRecord = (@pageindex-1)*@pagesize + 1
SET @sql=NSELECT * FROM (SELECT ROW_NUMBER() OVER (+ @orderstr +N) AS rowId,+@fields+N FROM + @tablename+N) AS T WHERE rowId>=+STR(@StartRecord)+N and rowId<=+STR(@StartRecord + @pagesize - 1)
EXEC(@sql)
END
GO


--根据TOP ID
CREATE PROC [dbo].[proc_select_page_top]
@pageindex INT=1,--当前页数
@pagesize INT=10,--每页大小
@tablename VARCHAR(50)=,--表名
@fields VARCHAR(1000)=,--查询的字段集合
@keyid VARCHAR(50)=,--主键
@condition NVARCHAR(1000)=,--查询条件
@orderstr VARCHAR(500),--排序条件
@totalRecord BIGINT OUTPUT--总记录数
AS
IF ISNULL(@orderstr,N)=N SET @orderstr=N ORDER BY +@keyid+N DESC
IF ISNULL(@fields,N)=N SET @fields=N*
IF ISNULL(@condition,N)=N SET @condition=N1=1
DECLARE @sql NVARCHAR(4000)
--IF(@totalRecord IS NULL)
--BEGIN
SET @sql=NSELECT @totalRecord=COUNT(*)
+N FROM +@tablename
+N WHERE +@condition
EXEC sp_executesql @sql,N@totalRecord INT OUTPUT,@totalRecord OUTPUT
--END
IF(@pageindex=1)
BEGIN
SET @sql=NSELECT TOP +STR(@pagesize)+N +@fields+N FROM +@tablename+N WHERE +@condition+N +@orderstr
EXEC(@sql)
END
ELSE
BEGIN
SET @sql=NSELECT TOP +STR(@pagesize)+N +@fields+N FROM +@tablename+N WHERE +@keyid
+N NOT IN(SELECT TOP +STR((@pageindex-1)*@pagesize)+N +@keyid+N FROM
+@tablename+N WHERE +@condition+N +@orderstr+N) AND +@condition+N +@orderstr
EXEC(@sql)
END
GO