用存儲過程來寫分頁功能

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

-- =============================================

-- Author:  grace

-- Create date: 2009-12-5

-- Description: 分頁過程

-- =============================================

ALTER  PROCEDURE [dbo].[Pagination]

(

@Tables varchar(1000),

@PK varchar(100),

@Sort varchar(200) = NULL,

@isAscending bit=1,

@PageNumber int = 1,

@PageSize int = 10,

@Fields varchar(1000) = '*',

@Filter varchar(1000) = NULL,

@Group varchar(1000) = NULL,

@totalcounts int output

)

AS

IF @Sort IS NULL OR @Sort = ''

 SET @Sort = @PK

set @totalcounts=0;

DECLARE @strFilter varchar(1000)

DECLARE @strSimpleFilter varchar(1000)

DECLARE @strGroup varchar(1000)

DECLARE @SortTable varchar(100)

DECLARE @SortName varchar(100)

DECLARE @strPKColumn varchar(200)

DECLARE @strSortColumn varchar(200)

DECLARE @operator char(2)

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

 BEGIN

  SET @strFilter = ' WHERE ' + @Filter + ' '

  SET @strSimpleFilter = ' AND ' + @Filter + ' '

 END

ELSE

 BEGIN

  SET @strSimpleFilter = ' '

  SET @strFilter = ' '

 END

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

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

ELSE

begin

 SET @strGroup = ' '

end

declare @sqlStr nvarchar(1000)

set @sqlStr = N'select  @totalcounts=count(*) from ' + @Tables + ' ' + @strFilter

EXEC sp_executesql @sqlStr,N'@totalcounts int OUTPUT', @totalcounts OUTPUT

SET @strSortColumn [email protected]

declare @strAsc_Des varchar(10)

IF @isAscending = 0

 BEGIN

  SET @operator = '<='

  SET @strAsc_Des = ' Desc '

 END

ELSE

 BEGIN

  SET @operator = '>='

  SET @strAsc_Des = ' ASC '

 END

IF CHARINDEX('.', @PK) > 0

 BEGIN

  SET @strPKColumn = SUBSTRING(@PK, 0, CHARINDEX('.',@PK)) 

 END

ELSE

 BEGIN

  SET @strPKColumn = @PK 

 END

IF CHARINDEX('.', @strSortColumn) > 0

 BEGIN

  SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))

  SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))

 END

ELSE

 BEGIN

  SET @SortTable = @Tables

  SET @SortName = @strSortColumn

 END

--Be join table,so get the left table

IF CHARINDEX(' join ',@Tables) > 0

BEGIN

SET @SortTable=SUBSTRING(ltrim(@Tables),0,CHARINDEX(' ',@Tables))

SET @strSortColumn = @SortTable+'.'[email protected]

END

DECLARE @tempName varchar(100)

IF @SortName <> @strPKColumn

begin

SELECT @tempName=b.name

FROM sysobjects a INNER JOIN

      sysobjects b ON a.id = b.parent_obj

    INNER JOIN sysindexes c ON b.name = c.name INNER JOIN

      sysindexkeys d ON c.id = d.id AND c.indid = d.indid INNER JOIN

      syscolumns e ON d.id = e.id AND d.colid = e.colid

WHERE (b.xtype = 'UQ') AND (a.name = @SortTable) AND (e.name = @SortName)

IF @tempName IS NULL GOTO Paging_Not_In

end

DECLARE @type varchar(100)

DECLARE @prec int

SELECT @type=t.name, @prec=c.prec

FROM sysobjects o

JOIN syscolumns c on o.id=c.id

JOIN systypes t on c.xusertype=t.xusertype

WHERE o.name = @SortTable AND c.name = @SortName

--IF  @type is null or @type=''  Goto:Paging_Not_In

IF CHARINDEX('char', @type) > 0

   SET @type = @type + '(' + CAST(@prec AS varchar) + ')'

DECLARE @strPageSize varchar(50)

DECLARE @strStartRow varchar(50)

IF @PageNumber < 1

 SET @PageNumber = 1

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

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

EXEC( 'DECLARE @SortColumn ' + @type + '

SET ROWCOUNT ' + @strStartRow +

 'SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup +

 ' ORDER BY ' + @Sort + @strAsc_Des+' SET ROWCOUNT ' + @strPageSize +

 'SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' +

 @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + @strAsc_Des

)

return

Paging_Not_In:

--DECLARE @strPageSize varchar(50)

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

declare @strTotalNum int

SET @strTotalNum = (@PageNumber - 1)*@PageSize

--第一頁

--Declare @strSQL varchar(8000)

IF  @strTotalNum = 0

begin

exec('select top '[email protected]+' '[email protected]+' from '[email protected]+' '[email protected]+ @strGroup + ' ORDER BY ' + @Sort + @strAsc_Des) 

end

else

begin

 exec('select top '[email protected]+' '[email protected]+' from '[email protected]+'  where '

[email protected]+' not in(select top '

[email protected]+' '[email protected]+' from '[email protected]+' '[email protected]+ @strGroup +

 ' ORDER BY ' + @Sort + @strAsc_Des

+') '[email protected]+ @strGroup + ' ORDER BY ' + @Sort + @strAsc_Des)

end

return;