sql分页

参数说明:

1.Tables :表名称,视图，不带dbo.

2.PrimaryKey :主关键字

3.Sort :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc (Desc Asc前必须加空格)

4.CurrentPage :当前页码

5.PageSize :分页尺寸

6.Fields: 字段名

7.Filter :过滤语句，不带Where

8.Group :Group语句,不带Group By

***************************************************************/

(

@Tables varchar(1000),

@PrimaryKey varchar(100) = NULL,

@Sort varchar(200) = NULL,

@CurrentPage int = 1,

@PageSize int = 20,

@Fields varchar(1000) = '*',

@Filter varchar(1000) = NULL,

@Group varchar(1000) = NULL

)

AS

BEGIN

SET NOCOUNT ON

/*默认排序*/

SET @Sort = RTRIM(LTRIM(@Sort))

IF @Sort IS NULL OR @Sort = ''

SET @Sort = @PrimaryKey

DECLARE @SortTable varchar(100)

DECLARE @SortName varchar(100)

DECLARE @SQL1 varchar(4000)

/*设定排序语句.*/

DECLARE @strPageSize varchar(50)

DECLARE @strStartRow varchar(50),@strEndRow varchar(50)

DECLARE @strFilter varchar(1000)

DECLARE @strGroup varchar(1000)

/*默认当前页*/

IF @CurrentPage < 1

SET @CurrentPage = 1

/*设置分页参数.*/

SET @strPageSize = CAST(@PageSize AS varchar(50))

SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))

SET @strEndRow = CAST((@CurrentPage*@PageSize) AS varchar(50))

/*筛选以及分组语句.*/

IF @Filter IS NOT NULL AND @Filter != ''

BEGIN

SET @strFilter = ' Where ' + @Filter + ' '

END

ELSE

BEGIN

SET @strFilter = ''

END

IF @Group IS NOT NULL AND @Group != ''

BEGIN

SET @strGroup = ' GROUP BY ' + @Group + ' '

END

ELSE

BEGIN

SET @strGroup = ''

END

/*执行查询语句*/

SET @SQL1 ='select * from (Select ' + @Fields + ',ROW_NUMBER() OVER(Order By '+@Sort+') as row FROM ' + @Tables + @strFilter + @strGroup + ') a where row between '+@strStartRow+' and '+@strEndRow

--PRINT (@SQL1)

EXEC (@SQL1)

END