分页存储过程

最新推荐文章于 2024-09-05 18:50:50 发布

woods240

最新推荐文章于 2024-09-05 18:50:50 发布

阅读量302

点赞数

分类专栏：零散的技术积累文章标签：存储 insert output

本文链接：https://blog.youkuaiyun.com/woods240/article/details/7200835

版权

零散的技术积累专栏收录该内容

33 篇文章

订阅专栏

CREATE PROCEDURE [spDo_PagingOrderFilter] 
	@SetName varchar(40),			-- 表或视图的名字
	@PageIndex int,					-- 第几页
	@PageSize int,					-- 页大小
	@OrderExpression varchar(100),	-- 排序表达式(Order By ...)
	@FilterExpression varchar(200), -- 过滤条件(Where ...)
	@TotalSize int output			-- 总记录数
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @strSql nvarchar(2000)

	SET @strSql = 'SET @TotalSizeOut = (SELECT COUNT(*) FROM [' + @SetName + '] ' + @FilterExpression + ')';
	EXEC SP_EXECUTESQL @strSql,N'@TotalSizeOut int output',@TotalSizeOut=@TotalSize output;

	SET @strSql = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (' + @OrderExpression + ') AS RowNum,* FROM [' + @SetName + '] ' + @FilterExpression + ')';
	SET @strSql = @strSql + ' AS T WHERE RowNum BETWEEN ' + STR((@PageIndex-1)*@PageSize+1) + ' AND ' + STR(@PageIndex*@PageSize);
    
    EXEC(@strSql)
END