SQL SERVER 分页存储过程(支持SQLSERVER2008)

/****** Object: Procedure [dbo].[SP_PAGING]   Script Date: 2010-12-2 13:56:37 ******/
USE [TEST_DB];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE [dbo].[SP_PAGING]
@TBL_NM nvarchar(4000) = '', @PAGE int = 1, @PAGESIZE int = 10, @SEARCH nvarchar(1000) = '', @TABLESORT nvarchar(1000) = 'ORDER BY Idx DESC'
WITH
EXECUTE AS CALLER
AS
BEGIN
    SET NOCOUNT ON
   
    DECLARE @ALIAS  NVARCHAR(1)
    DECLARE @SQL    NVARCHAR(4000)   -- sql
    DECLARE @COLUMN NVARCHAR(4000)   -- 字段

    DECLARE @FROM   NVARCHAR(4000)   -- FROM 表名

    DECLARE @WHERE  NVARCHAR(4000)   -- WHERE子句
    
    SET @COLUMN = ' * '
    SET @FROM = ' FROM ' + @TBL_NM
    SET @WHERE = ' WHERE 1=1 '
    --SET @SQL = ' SELECT COUNT(1) AS CNT ' + @FROM + @WHERE + @SEARCH
   
    --EXEC(@SQL)
    --PRINT @SQL
    SET @SQL = ' SELECT T.RowNum rownum,' + @COLUMN +
               ' FROM (SELECT Row_Number()OVER(' + @TABLESORT + ') AS RowNum, ' +
                 @COLUMN + @FROM + @WHERE + @SEARCH + ') AS T' +
               ' WHERE T.RowNum BETWEEN ' + CONVERT(VARCHAR, ((@PAGE - 1) * @PAGESIZE) + 1) +
               '   AND ' + CONVERT(VARCHAR, @PAGE * @PAGESIZE)
    EXEC(@SQL)
    --PRINT @SQL
    SET NOCOUNT OFF
END
GO

 

 

用法：

PreparedStatement：

1.一个排序字段

 StringBuilder sql = new StringBuilder();
    sql
      .append("(SELECT  A.TYPE AS Idx, A.NAME AS Name              ").append("/n")

      .append("    FROM TABLE A                                                        ").append("/n")

      .append(" ) X                                                                             ").append("/n");
   
 
    conn = ConnectionPoolManager.getInstance().getConnection();
    pst = conn.prepareStatement("EXEC SP_PAGING ?,?,?,?");  //参数1到5个
    pst.setString(1, sql.toString());
    pst.setInt(2, pageNumber);
    pst.setInt(3, pageSize);
    pst.setString(4, whereStr.toString());
    rs = pst.executeQuery();

2.多个排序字段

    conn = ConnectionPoolManager.getInstance().getConnection();
    pst = conn.prepareStatement("EXEC SP_PAGING ?,?,?,?,?");
    pst.setString(1, sql.toString());
    pst.setInt(2, pageNumber);
    pst.setInt(3, pageSize);
    pst.setString(4, whereStr.toString());
    pst.setString(5, " ORDER BY StatusCode ASC,EndDate ASC ");

    rs = pst.executeQuery();

CallableStatement：

cstmt = conn.prepareCall("{CALL SP_PAGING(?,?,?)}");
        cstmt.setString(1,sql.toString());
        cstmt.setInt(2,pageNumber);
        cstmt.setInt(3,pageSize);
        cstmt.execute();
        rs = cstmt.getResultSet();