sqlserver 存储过程分页

概述USE [HK_ERP_Report]GO/****** Object: StoredProcedure [dbo].[spPF_AppRpt_Tupu_CommonPage] Script Date: 2019/4/4 12:00:23 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/*DECLAR

USE [HK_ERP_Report]GO/****** Object:  StoredProcedure [dbo].[spPF_AppRpt_Tupu_CommonPage]    Script Date: 2019/4/4 12:00:23 ******/SET ANSI_NulLS ONGOSET QUOTED_IDENTIFIER ONGO/*DECLARE	@return_value int,@PageCount int,@TotalRecord intEXEC	@return_value = [dbo].[spPF_AppRpt_Tupu_CommonPage]		@tablename = N‘PF_Shopper‘,@ReFIEldsstr = N‘*‘,@OrderString = N‘LastVisitTime asc‘,@PageSize = 10,@PageIndex = 1,@TotalRecord = @TotalRecord OUTPUT,@PageCount = @PageCount OUTPUTSELECT	@TotalRecord as N‘@TotalRecord‘,@PageCount as N‘@PageCount‘SELECT	‘Return Value‘ = @return_value*/ALTER PROCEDURE [dbo].[spPF_AppRpt_Tupu_CommonPage]    @tablename VARCHAR(50),           --表名    @ReFIEldsstr VARCHAR(200) = ‘*‘,  --字段名(全部字段为*)    @OrderString VARCHAR(200),        --排序字段(必须!支持多字段不用加order by)    @WhereString VARCHAR(500) =N‘‘, --条件语句(不用加where)    @PageSize INT = 10,                    --每页多少条记录    @PageIndex INT = 1,              --指定当前为第几页	@PageCount INT = 0 OUTPUT,--总页数    	@TotalRecord INT OUTPUT            --返回总记录数ASBEGIN     --处理开始点和结束点    DECLARE @StartRecord INT;    DECLARE @EndRecord INT;     DECLARE @TotalCountsql NVARCHAR(500);     DECLARE @sqlString NVARCHAR(2000);        SET @StartRecord = (@PageIndex-1)*@PageSize + 1--起始记录    SET @EndRecord = @StartRecord + @PageSize - 1 --结尾记录    SET @TotalCountsql= N‘select @TotalRecord = count(*) from ‘ + @tablename;--总记录数语句    SET @sqlString = N‘(select row_number() over (order by ‘+ @OrderString +‘) as rowID,‘[email protected]+‘ from ‘+ @tablename;--查询语句    --    IF (@WhereString! = ‘‘ or @WhereString!=null)        BEGIN            SET @[email protected] + ‘ where ‘+ @WhereString;            SET @sqlString [email protected]+ ‘ where ‘+ @WhereString;                    END    --第一次执行得到    EXEC sp_executesql @totalCountsql,N‘@TotalRecord int out‘,@TotalRecord OUTPUT;--返回总记录数	SET @[email protected]/@pageSize+(CASE WHEN @TotalRecord%@pageSize>0 THEN 1 ELSE 0 END)    ----执行主语句    SET @sqlString =‘select * from ‘ + @sqlString + ‘) as t where rowID between ‘ + ltrim(str(@StartRecord)) + ‘ and ‘ +  ltrim(str(@EndRecord));    Exec(@sqlString)    END

总结

以上是内存溢出为你收集整理的sqlserver 存储过程分页全部内容，希望文章能够帮你解决sqlserver 存储过程分页所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。