SQL Server 存储过程分页示例代码

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

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

代码如下:

--分页存储过程示例 
Alter PROCEDURE [dbo].[JH_PageDemo] 
@pageSize int = 9000000000, 
@pageIndex int = 1 , 
@orderBy Nvarchar(200) = '' -- 不加order By 
AS 
SET NOCOUNT ON 
--声明变量 
DECLARE @select VARCHAR(3048); 
DECLARE @from VARCHAR(512); 
DECLARE @RowNumber VARCHAR(256); 
DECLARE @condition nVARCHAR(3990); 
DECLARE @groupBy varchar(50); 
DECLARE @sql VARCHAR(3998); 
DECLARE @RowStartIndex INT; 
DECLARE @RowEndIndex INT; 
BEGIN 
SET NOCOUNT on 
IF @orderBy <> '' 
Set @orderBy = ' ORDER BY ' + @orderBy; 
else 
Set @orderBy = ' ORDER BY Userid ' ; 
SET @select = ' select userid,username ,'; 
--设置排序语句 
SET @RowNumber ='ROW_NUMBER() OVER (' + @orderBy + ' ) as RowNumber '; 
SET @select = @select + @RowNumber; 
SET @from = ' FROM users '; 
--设置条件语句@GULevel 
SET @condition = ' WHERE 1=1 '; 
SET @condition = @condition + 'AND userid > 0'; 
--分组语句 
SET @groupBy = ' GROUP BY USerID ' 
SET @RowStartIndex = ( @pageIndex -1) * @pageSize + 1 
SET @RowEndIndex = @pageIndex * @pageSize ; 
--查询结果 
SET @sql = 'SET NOCOUNT ON; 
WITH ResultTable AS ( ' + @select + @from + @condition +') 
SELECT * FROM ResultTable WHERE RowNumber between ' + 
Cast(@RowStartIndex AS VARCHAR(32)) + ' AND ' + CAST(@RowEndIndex AS VARCHAR(32)) 
+ ' ; SELECT count(*) as totalcount ' + @from + @condition + ' ' 

--PRINT @sql; 
EXEC(@sql); 
END

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