SQL Server 三种SQL分页查询的存储过程示例

这篇文章主要为大家详细介绍了SQL Server 三种SQL分页查询的存储过程示例，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

代码如下:

 
--根据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=N'1=1' 
DECLARE @sql NVARCHAR(4000) 
--IF(@totalRecord IS NULL) 
--BEGIN 
SET @sql=N'SELECT @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=N'SELECT 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=N'SELECT 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=N'1=1' 
DECLARE @sql NVARCHAR(4000) 
-- IF @totalRecord IS NULL 
-- BEGIN 
SET @sql=N'SELECT @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=N'SELECT 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=N'SELECT * 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=N'1=1' 
DECLARE @sql NVARCHAR(4000) 
--IF(@totalRecord IS NULL) 
--BEGIN 
SET @sql=N'SELECT @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=N'SELECT TOP '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE '+@condition+N' '+@orderstr 
EXEC(@sql) 
END 
ELSE 
BEGIN 
SET @sql=N'SELECT 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 

注：关于SQL Server 三种SQL分页查询的存储过程示例的内容就先介绍到这里，更多相关文章的可以留意