存储过程分页

最新推荐文章于 2020-11-20 12:30:37 发布

iteye_20755

最新推荐文章于 2020-11-20 12:30:37 发布

阅读量83

点赞数

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为“'%%'”