四个通用分页存储过程(来源于www.codeproject.com)

  1--SELECT NAME FROM SYSOBJECTS WHERE TYPE = 'U'
  2----显示数据库的所有表的属性值
  3use bajsyy
  4GO
  5
  6select
  7     ( case when a.colorder = 1 then d.name else '' end ) 表名,
  8     a.colorder 字段序号,
  9     a.name 字段名,
 10     ( case when COLUMNPROPERTY (a.id,a.name,'isidentity') = 1 then '√' else '' end ) 标识,
 11     ( case when (
 12            select count(*) from sysobjects
 13                where name in (
 14                    select name from sysindexes
 15                    where (id = a.id ) and ( indid in
 16                        (select indid from sysindexkeys where
 17                            ( id = a.id ) and ( colid in (
 18                                select colid from syscolumns
 19                                    where ( id = a.id ) and ( name = a.name ))))))
 20                    and ( xtype ='PK')) > 0 then '√' else '' end ) 主键,
 21    b.name 类型,
 22    a.length 字节数,
 23    COLUMNPROPERTY ( a.id,a.name ,'PRECISION' ) as 长度,
 24    isnull ( COLUMNPROPERTY ( a.id,a.name ,'Scale'),0) as 小数位数,
 25    (case when a.isnullable = 1 then '√' else '' end ) 允许空,
 26    isnull ( e.text,'') 默认值,
 27    isnull (g.[value],'' ) as 字段说明
 28from syscolumns a left join systypes b
 29on a.xtype = b.xusertype
 30inner join sysobjects d
 31on a.id = d.id and d.xtype='U' and d.name <> 'dtproperties'
 32left join syscomments e
 33on a.cdefault = e.id
 34left join sysproperties g
 35on a.id = g.id and a.colid = g.smallid
 36order by a.id ,a.colorder
 37
 38
 39----------分页代码测试
 40/**//*
 41----------------------------------------------------
 42        spDataPaging 1.0.0.3
 43----------------------------------------------------
 44Autor:    Luis Ruiz Arauz        Date:     2005/06/12        
 45----------------------------------------------------
 46Makes a paged query with the sql parameters to build it
 47and page parameters to filter.
 48Add "No","CurrentPage","TotalPages" coumns to the query
 49to manage paging at user interface.
 50The query Lose the identity column order
 51It works fine for me!
 52----------------------------------------------------
 53Samples:
 54EXEC spDataPaging 'VistaOrders','OrderID,CustomerID','OrderID > 10258','','',15,30
 55EXEC spDataPaging 'VistaOrders','COUNT(OrderID)OrdersCount ,CustomerId','','CustomerID','CustomerID',2,30
 56EXEC spDataPaging 'Customers','*','','','',2,30
 57
 58EXEC spDataPaging 'BirthControl','*','',2,30
 59----------------------------------------------------
 60BphyID CardDate Be_Scrutator  View_BirthControlList
 61EXEC spDataPaging 'View_BirthControlList','*','',1,20
 62*/
 63IF EXISTS (SELECT name FROM sysobjects
 64         WHERE name = 'spDataPaging' AND type = 'P')
 65 DROP PROCEDURE spDataPaging
 66GO
 67CREATE PROC spDataPaging
 68    @TableOrView    VARCHAR(128),
 69    @Columns    VARCHAR(1000) = '*',
 70    @Criteria    VARCHAR(3000) = '',
 71    @Page        INTEGER = 1,
 72    @PageSize    INTEGER = 30
 73    
 74AS
 75DECLARE @TableTemp    VARCHAR(100),
 76    @IdentityName      VARCHAR(50),
 77    @IdentityType      VARCHAR(20),
 78    @SQLScript    VARCHAR(5000)
 79
 80SET NOCOUNT ON
 81
 82SET @Columns = REPLACE(@Columns,' ','')
 83
 84SET  @TableTemp = @TableOrView
 85
 86IF(LEN(@TableTemp) != LEN(REPLACE(@TableTemp,'..','')))
 87BEGIN
 88    SET @TableTemp =  RIGHT(@TableTemp,LEN(@TableTemp) - CHARINDEX('..',@TableTemp) - 1)
 89END
 90
 91SELECT @IdentityName = b.name,@IdentityType = c.DATA_TYPE
 92FROM sysobjects a, syscolumns b,Information_Schema.COLUMNS c
 93WHERE a.id = b.id
 94AND a.name = @TableTemp
 95AND c.TABLE_NAME = a.name
 96AND c.COLUMN_NAME = b.name
 97AND c.TABLE_CATALOG = DB_NAME()
 98AND b.status = 128
 99
SET  @TableTemp = '#' + @TableTemp

IF     @IdentityName IS NOT NULL
    AND ((LEN(@Columns) != LEN(REPLACE(@Columns,@IdentityName,'')))OR @Columns = '*')
    AND ((LEN(@Columns) = LEN(REPLACE(@Columns,'(' + @IdentityName + ')',''))) OR @Columns = '*')
 BEGIN
    SELECT @SQLScript =     ' SELECT ' + @Columns  + ',CAST('  + @IdentityName +  ' AS ' + @IdentityType + ') Num INTO ' + @TableTemp + ' FROM ' + @TableOrView + ' ' + @Criteria +
                ' ALTER TABLE ' + @TableTemp + ' DROP COLUMN '  + @IdentityName +
                ' ALTER TABLE ' + @TableTemp + ' ADD No ' + @IdentityType + ' IDENTITY' +
                ' SELECT * INTO ' + @TableTemp +  '2 FROM ' + @TableTemp +
                ' SELECT *,CAST(Num AS ' + @IdentityType + ') ' + @IdentityName + ' INTO ' + @TableTemp + '3 FROM ' + @TableTemp + '2 WHERE (No BETWEEN ' + CAST((@Page * @PageSize - @PageSize + 1) AS VARCHAR(20)) + ' AND ' + CAST((@Page * @PageSize) AS VARCHAR(20)) +




 ')' +
                ' ALTER TABLE ' + @TableTemp + '3 DROP COLUMN Num' +
                ' SELECT *,' + CAST(@Page AS VARCHAR(20)) + ' CurrentPage,CEILING(((SELECT COUNT(*) FROM ' + @TableTemp + '2) - 1)/' + CAST(@PageSize AS VARCHAR(20)) + ') + 1 TotalPages FROM ' +  @TableTemp + '3'
 END
ELSE
 BEGIN
    SELECT @SQLScript =     ' SELECT ' + @Columns  + ' INTO ' + @TableTemp + ' FROM ' + @TableOrView + ' ' + @Criteria +
                ' ALTER TABLE ' + @TableTemp + ' ADD No INT IDENTITY' +
                ' SELECT * INTO ' + @TableTemp +  '2 FROM ' + @TableTemp +
                ' SELECT * INTO ' + @TableTemp + '3 FROM ' + @TableTemp + '2 WHERE (No BETWEEN ' + CAST((@Page * @PageSize - @PageSize + 1) AS VARCHAR(20)) + ' AND ' + CAST((@Page * @PageSize) AS VARCHAR(20)) + ')' +
                ' SELECT *,(SELECT COUNT(*) FROM ' + @TableTemp + '2) TotalRows,' + CAST(@Page AS VARCHAR(20)) + ' CurrentPage,CEILING(((SELECT COUNT(*) FROM ' + @TableTemp + '2) - 1)/' + CAST(@PageSize AS VARCHAR(20)) + ') + 1 TotalPages FROM ' +  @TableTemp + '3'
 END

--SELECT @SQLScript

EXEC(@SQLScript)



----2-------------------------------------------分页SQL代码2
IF EXISTS (SELECT name FROM sysobjects
         WHERE name = 'SP_Pagination' AND type = 'P')
 DROP PROCEDURE SP_Pagination
GO
CREATE  PROCEDURE SP_Pagination
/**//**//**//*
***************************************************************
** 千万数量级分页存储过程 **
***************************************************************
参数说明:
1.Tables :表名称,视图
2.PrimaryKey :主关键字
3.Sort :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc
4.CurrentPage :当前页码
5.PageSize :分页尺寸
6.Filter :过滤语句，不带Where
7.Group :Group语句,不带Group By
View_BirthControlList

EXEC SP_Pagination 'View_BirthControlList','证明号','',1,10,'*','',''
***************************************************************/
(
@Tables varchar(2000),
@PrimaryKey varchar(500),
@Sort varchar(500) = NULL,
@CurrentPage int = 1,
@PageSize int = 10,
@Fields varchar(2000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL
)
AS
/**//**//**//*默认排序*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar(1000)
DECLARE @SortName varchar(1000)
DECLARE @strSortColumn varchar(1000)
DECLARE @operator char(2)
DECLARE @type varchar(1000)
DECLARE @prec int
/**//**//**//*设定排序语句.*/
IF CHARINDEX('DESC',@Sort)>0
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
SET @operator = '<='
END
ELSE
BEGIN
IF CHARINDEX('ASC', @Sort) = 0
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
SET @operator = '>='
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
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 CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
DECLARE @strPageSize varchar(500)
DECLARE @strStartRow varchar(500)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)
/**//**//**//*默认当前页*/
IF @CurrentPage < 1
SET @CurrentPage = 1
/**//**//**//*设置分页参数.*/
SET @strPageSize = CAST(@PageSize AS varchar(500))
SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(500))
/**//**//**//*筛选以及分组语句.*/
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
SET @strGroup = ''
/**//**//**//*执行查询语句*/
EXEC(
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
'
)



-------------------------3分页

CREATE PROCEDURE dbo.up_GetSortedSalesOrdersByPageUsingRowset
    @orderedOnStart datetime,
    @orderedOnEnd datetime,
    @pageNumber int,
    @pageSize int,
    @sortExpression varchar(100),
    @sortOrder varchar(4),
    @virtualCount int OUTPUT
AS

    /**//*
        Make sure that the page number is at least 1
    */
    IF @pageNumber < 1
    BEGIN
        SET @pageNumber = 1
    END

    SELECT
        @virtualCount = COUNT(*)
    FROM
        Sales.SalesOrderHeader Header
    WHERE
        Header.[OrderDate] >= @orderedOnStart
        AND Header.[OrderDate] < @orderedOnEnd

    DECLARE @lastKeyValue numeric(18,0)
    DECLARE @lastAscendingSortValue SQL_Variant
    DECLARE @lastDescendingSortValue SQL_Variant

    DECLARE @numberToIgnore int

    SET @numberToIgnore = (@pageNumber-1) * @pageSize

    IF @numberToIgnore > 0
    BEGIN
        /**//*
        Get the last available sort data and unique key
        value from the last page.
        */
        SET ROWCOUNT @numberToIgnore

        SELECT
            @lastKeyValue = [UniqueValue],
            @lastAscendingSortValue = [AscendingSort],
            @lastDescendingSortValue = [DescendingSort]
        FROM
        (
            SELECT
                Header.[SalesOrderID] AS [UniqueValue],
                CASE
                    WHEN
                        UPPER(@sortOrder) = 'DESC'
                        AND UPPER(@sortExpression) = 'CUSTOMERID'
                    THEN
                        CONVERT(SQL_Variant, [CustomerID])
                    WHEN
                        UPPER(@sortOrder) = 'DESC'
                        AND UPPER(@sortExpression) = 'TOTALDUE'
                    THEN
                        CONVERT(SQL_Variant, [TotalDue])
                    WHEN
                        UPPER(@sortOrder) = 'DESC'
                        AND UPPER(@sortExpression) = 'ORDERDATE'
                    THEN
                        CONVERT(SQL_Variant, [OrderDate])
                    ELSE
                        NULL
                END AS [DescendingSort],
                CASE
                    WHEN
                        UPPER(@sortOrder) = 'ASC'
                        AND UPPER(@sortExpression) = 'CUSTOMERID'
                    THEN
                        CONVERT(SQL_Variant, [CustomerID])
                    WHEN
                        UPPER(@sortOrder) = 'ASC'
                        AND UPPER(@sortExpression) = 'TOTALDUE'
                    THEN
                        CONVERT(SQL_Variant, [TotalDue])
                    WHEN
                        UPPER(@sortOrder) = 'ASC'
                        AND UPPER(@sortExpression) = 'ORDERDATE'
                    THEN
                        CONVERT(SQL_Variant, [OrderDate])
                    ELSE
                        NULL
                END AS [AscendingSort]
            FROM
                Sales.SalesOrderHeader Header
            WHERE
                Header.[OrderDate] >= @orderedOnStart
                AND Header.[OrderDate] < @orderedOnEnd
            ) AS Derived
        ORDER BY
            [AscendingSort] ASC,
            [DescendingSort] DESC,
            [UniqueValue] ASC
    END

    /**//*
    Select the first @pageSize records that come after the last sort
    data/unique value from the last page. If this is the first page,
    just get the first @pageSize records.
    */

    SET ROWCOUNT @pageSize

    SELECT
        [SalesOrderID],
        [OrderDate],
        [TotalDue],
        [CustomerID]
    FROM
    (
        SELECT
            [SalesOrderID],
            [OrderDate],
            [TotalDue],
            [CustomerID],
            [SalesOrderID] As [UniqueValue],
            CASE
                WHEN
                    UPPER(@sortOrder) = 'DESC'
                    AND UPPER(@sortExpression) = 'CUSTOMERID'
                THEN
                    CONVERT(SQL_Variant, [CustomerID])
                WHEN
                    UPPER(@sortOrder) = 'DESC'
                    AND UPPER(@sortExpression) = 'TOTALDUE'
                THEN
                    CONVERT(SQL_Variant, [TotalDue])
                WHEN
                    UPPER(@sortOrder) = 'DESC'
                    AND UPPER(@sortExpression) = 'ORDERDATE'
                THEN
                    CONVERT(SQL_Variant, [OrderDate])
                ELSE
                    NULL
            END AS [DescendingSort],
            CASE
                WHEN
                    UPPER(@sortOrder) = 'ASC'
                    AND UPPER(@sortExpression) = 'CUSTOMERID'
                THEN
                    CONVERT(SQL_Variant, [CustomerID])
                WHEN
                    UPPER(@sortOrder) = 'ASC'
                    AND UPPER(@sortExpression) = 'TOTALDUE'
                THEN
                    CONVERT(SQL_Variant, [TotalDue])
                WHEN
                    UPPER(@sortOrder) = 'ASC'
                    AND UPPER(@sortExpression) = 'ORDERDATE'
                THEN
                    CONVERT(SQL_Variant, [OrderDate])
                ELSE
                    NULL
            END AS [AscendingSort]
        FROM
            Sales.SalesOrderHeader Header
        WHERE
            Header.[OrderDate] >= @orderedOnStart
            AND Header.[OrderDate] < @orderedOnEnd
    ) Derived
    WHERE
    (
        @lastAscendingSortValue IS NULL
        AND @lastDescendingSortValue IS NULL
        AND @lastKeyValue IS NULL
    )
    OR
    (
        (@lastAscendingSortValue IS NOT NULL)
        AND
        (
            ([AscendingSort] > @lastAscendingSortValue)
            OR
            (
                [AscendingSort] = @lastAscendingSortValue
                AND [UniqueValue] > @lastKeyValue
            )
        )
    )
    OR
    (
        (@lastDescendingSortValue IS NOT NULL)
        AND
        (
            ([DescendingSort] < @lastDescendingSortValue)
            OR
            (
                [DescendingSort] = @lastDescendingSortValue
                AND [UniqueValue] > @lastKeyValue
            )
        )
    )
    ORDER BY
        [AscendingSort] ASC,
        [DescendingSort] DESC,
        [SalesOrderID] ASC

    SET ROWCOUNT 0
GO


-------------4分页

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

    ALTER  PROCEDURE sp_Easypaging

    (
   
    @Tables varchar(1000),
    @PK varchar(100),
    @JoinStatements varchar(1000)='',
    @Fields varchar(5000) = '*',
    @Filter varchar(5000) = NULL,
    @Sort varchar(200) = NULL,
    @PageNumber int = 1,
    @PageSize int = 10,
    @TotalRec int =0 Output,
    @Group varchar(1000) = NULL
   
       
    )

AS



/**//*
Created by Kashif Akram
Email Muhammad_kashif@msn.com

The publication rights are reserved 
You can use this procedure with out removing these comments
*/



DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)


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



--set @PK =' tbl_Items.ItemID '

create table #PageTable (PID  bigint primary key IDENTITY (1, 1) , UID int)
create table #PageIndex (UID int)

/**//*
CREATE UNIQUE CLUSTERED
  INDEX [PK_tbl_PageTable] ON #PageTable (PID)
*/
CREATE 
  INDEX [PK_tbl_PageIndex] ON #PageIndex (UID)


--'SELECT ' + @Fields + ' FROM ' + @Tables + '' + @JoinStatements +' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort +  ' DESC '
exec ('

set rowcount 0

    insert into #pageTable(UID)
     SELECT ' + @PK + ' FROM ' + @Tables + ' ' + @JoinStatements +' WHERE ' +  @Filter + ' ' + @Group + ' ORDER BY ' + @Sort +  '


DECLARE @SortColumn int

SET ROWCOUNT '+  @strStartRow +'

select  @SortColumn=PID from #PageTable --option (keep plan)

print @SortColumn

SET ROWCOUNT '+  @strPageSize +'

insert into #pageIndex
select UID from #PageTable where PID >= @SortColumn -- option (keep plan)


         
SELECT ' + @Fields + ' FROM ' + @Tables + ' ' + @JoinStatements +' WHERE ' +  @Filter + ' and  '+ @PK + ' in (Select UID from #pageIndex)' + @Group + ' ORDER BY ' + @Sort +  ' '

 )



select @TotalRec=count(*) from  #pageTable





drop table #PageTable
drop table #PageIndex


   
RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO