最常用的一种存储过程分页方式，基于AspNetPager分布控件

ALTER PROCEDURE [dbo].[usp_UserGroup_Search] @pageSize AS INT ,@pageIndex AS INT ,@orderBy AS VARCHAR(100) = NULL ,@recordCount INT = 0 OUTPUT --output recordcount AS BEGIN SET NOCOUNT ON IF @orderBy IS NULL OR @orderBy = '' SET @orderBy = 'GroupId' DECLARE @sql AS NVARCHAR(1000) --get record count SET @sql = N'SELECT @recordCount=COUNT(*) FROM tbl_SysUserGroup ' EXEC sp_executesql @sql, N'@recordCount int output', @recordCount OUTPUT --list result set SET @sql = N' WITH temptbl AS ( SELECT ROW_NUMBER() OVER (ORDER BY ' + @orderBy + ') AS row , tsug.GroupId ,tsug.GroupName ,tsug.Remarks FROM tbl_SysUserGroup tsug ) SELECT * FROM temptbl ' IF @pageSize > 0 SET @sql = @sql + N' WHERE row BETWEEN ' + CONVERT(VARCHAR, (@pageIndex -1) * @pageSize + 1) + ' AND ' + CONVERT(VARCHAR, (@pageIndex -1) * @pageSize + @pageSize) EXEC (@sql) RETURN @recordCount; SET NOCOUNT OFF END