存储过程分页

来源：互联网发布：界面设计用技术软件编辑：程序博客网时间：2024/05/17 21:48

CREATE PROCEDURE GetData

@PageIndex int,
@PageSize int,
@order varchar(24), --排序字段
@method varchar(8), --排序方式
@where varchar(200), --条件语句
@RecordCount int output,
@PageCount int output

AS

declare @methodd varchar(8)

if @method = 'asc'
set @methodd = 'desc'
else if @method = 'desc'
set @methodd = 'asc'

SELECT @RecordCount=count(*) FROM [table] where col1 like +@where

SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)

DECLARE @SQLSTR NVARCHAR(300)

IF @PageIndex = 0 OR @PageCount <= 1

         SET @SQLSTR ='SELECT TOP '+STR( @PageSize )+

' * FROM [table] where col1 like '+@where+' order by '+@order+' '+@method

ELSE IF @PageIndex = @PageCount - 1
SET @SQLSTR ='SELECT TOP '+STR( @RecordCount - @PageSize * @PageIndex )

+' * FROM   [table] where col1 like '+@where+' order by '+@order+' '+@method

ELSE
SET @SQLSTR ='SELECT TOP '+STR( @PageSize )+' * FROM ( SELECT TOP '

+STR( @RecordCount - @PageSize * @PageIndex )+'   * FROM   [table] where col1 like '

+@where+' ORDER BY '+@order+' '+@methodd+' ) nTable order by '+@order+' '+@method

EXEC (@SQLSTR)

GO
------若没条件限制可传where为“'%%'”