USE [CapitalFortune]
GO
/****** Object: StoredProcedure [dbo].[SetMultiPages] Script Date: 05/30/2012 10:17:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: hugejile
-- alter date: 2070-9-29
-- Description: 分页,请勿随意改动
-- =============================================
ALTER PROCEDURE [dbo].[SetMultiPages]
-- Add the parameters for the stored procedure here
@TableName NVARCHAR(2000),
@ColumnNames NVARCHAR(2000),
@Conditions NVARCHAR(2000),
@OrderbyString NVARCHAR(2000),
@PageSize INT=1000,
@CurrentPage INT=1,
@RecordCount INT=0 OUTPUT,
@PageCount INT=1 OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets FROM
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Sql NVARCHAR(4000),
@RowCount INT
SET @RowCount=0
IF @ColumnNames is null or LEN(@ColumnNames)=0
BEGIN
SET @ColumnNames='*'
END
IF @CurrentPage is null
BEGIN
SET @CurrentPage=1
END
IF @PageSize is null
BEGIN
SET @PageSize=10
END
IF @Conditions is null
BEGIN
SET @Conditions=''
END
ELSE
BEGIN
IF LEN(@Conditions)<>0
BEGIN
SET @Conditions=' WHERE ' + @Conditions
END
END
-- INSERT statements for procedure here
IF @CurrentPage>0
BEGIN
SET @Sql= N'SELECT * FROM (SELECT '+@ColumnNames+', (ROW_NUMBER() over (ORDER BY '+@OrderbyString+')) AS RowNum FROM '+@TableName+@Conditions+' )T WHERE T.RowNum Between '+Cast((@CurrentPage-1)*@PageSize+1 AS NVARCHAR(10)) + ' and ' + Cast(@CurrentPage*@PageSize AS NVARCHAR(10)) + ';SET @RowCount =ROWCOUNT_BIG()'
PRINT 'SELECT CommandText: ' + @Sql
EXECUTE sp_executesql @Sql,N'@RowCount INT OUTPUT',@RowCount OUTPUT
SET @Sql = 'SELECT @RecordCount = COUNT(*) FROM '+@TableName+@Conditions
PRINT 'Count CommandText: ' + @Sql
EXECUTE sp_executesql @Sql,N'@RecordCount INT OUTPUT',@RecordCount OUTPUT
SET @PageCount=ceiling(1.0 * @RecordCount / @PageSize)
PRINT @RecordCount
--RETURN @RecordCount
END
ELSE IF @PageSize>0
BEGIN
SET @Sql= N'SELECT TOP ' + Cast(@PageSize as nvarchar(10)) + ' ' + @ColumnNames + ', (ROW_NUMBER() over (ORDER BY '+@OrderbyString+')) AS RowNum FROM '+@TableName+@Conditions+' ORDER BY ' + @OrderbyString
PRINT 'SELECT CommandText: ' + @Sql
EXEC (@Sql)
SET @Sql = 'SELECT @RecordCount = COUNT(*) FROM '+@TableName+@Conditions
PRINT 'Count CommandText: ' + @Sql
EXECUTE sp_executesql @Sql,N'@RecordCount INT OUTPUT',@RecordCount OUTPUT
SET @PageCount = CEILING(CAST(@RecordCount AS FLOAT)/CAST(@PageSize AS FLOAT))
--print cast(@RecordCount as nvarchar(10)) + ' pagesize ' + Cast(@PageSize as nvarchar(10))
--print @PageCount
--RETURN @RowCount
END
ELSE
BEGIN
SET @Sql= N'SELECT ' + @ColumnNames + ', (ROW_NUMBER() over (ORDER BY '+@OrderbyString+')) AS RowNum FROM '+@TableName+@Conditions+' ORDER BY ' + @OrderbyString +'; SET @RecordCount = ROWCOUNT_BIG()'
PRINT 'SELECT CommandText: ' + @Sql
--EXEC (@Sql)
--SET @Sql = ''
--PRINT 'Count CommandText: ' + @Sql
EXECUTE sp_executesql @Sql,N'@RecordCount INT OUTPUT',@RecordCount OUTPUT
SET @PageCount = -1
END
RETURN 1
END
这个是分页存储过程,百万级数据应该没有问题
调用方法
DECLARE @return_value INT,
@RecordCount INT ,
@PageCount INT ,
@PageSize INT
EXEC @return_value = [dbo].[SetMultiPages]
@TableName = 'USERS',
@ColumnNames = '*',
@Conditions = '',
@OrderbyString = 'UserId',
@PageSize = 10,
@CurrentPage = 0,
@RecordCount = @RecordCount output,
@PageCount = @PageCount output
SELECT @RecordCount AS N'RecordCount',
@PageCount AS N'PageCount',
@PageSize AS N'PageSize'
@RecordCount = @RecordCount output,
@PageCount = @PageCount output这两个参数是输出参数,用来接受存储过程中的输出参数的值,计算总数据和总页数。调用的时候必须说明输出参数的标志就是Output关键字标识!