分页存储过程

   int maxPage = 0;             int totalRow = 0;             SqlParameter[] _param ={                                         new SqlParameter("@CurrentPage",SqlDbType.Int, 4),                                         new SqlParameter("@PageSize", SqlDbType.Int, 4),                                         new SqlParameter("@Table", SqlDbType.NVarChar, 4000),                                         new SqlParameter("@Field", SqlDbType.NVarChar, 4000),                                         new SqlParameter("@OrderBy", SqlDbType.NVarChar, 100),                                          new SqlParameter("@Filter", SqlDbType.NVarChar, 500),                                         new SqlParameter("@MaxPage", SqlDbType.Int, 4),                                         new SqlParameter("@TotalRow", SqlDbType.Int, 4),                                         new SqlParameter("@Descript", SqlDbType.NVarChar, 100)                                          };             _param[0].Value = pageInex;             _param[1].Value = pageSize;             _param[2].Value = "VwOCOpenBoardDiscuss";             _param[3].Value = "OpenBoardDiscussID,Content,WriterID,ReceiverID,CreateTime,UserName,AssociateItemID,ImageUrl";             _param[4].Value = "CreateTime desc";             _param[5].Value = sqlWhere;             _param[6].Direction = ParameterDirection.Output;             _param[7].Direction = ParameterDirection.Output;             _param[8].Direction = ParameterDirection.Output;

            string strSql = "usp_CommonPage";             DataTable dt = SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringProfile, CommandType.StoredProcedure, strSql, _param);             maxPage = Convert.ToInt32(_param[6].Value);             totalRow = Convert.ToInt32(_param[7].Value);

 

 

USE [OpenSoftV1001] GO /****** Object:  StoredProcedure [dbo].[usp_CommonPage]    Script Date: 12/27/2011 11:15:40 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

-- ============================================= -- Author:  赵海 -- Create date: 2009.09.08 -- Description: 通用分页存储过程   /*   USE [AdventureWorksDW]   GO   DECLARE @start_time DATETIME   SELECT @start_time=GETDATE()

  DECLARE @return_value int,     @MaxPage smallint,     @TotalRow int,     @Descript varchar(100)

  EXEC @return_value = [dbo].[usp_CommonPage]     @CurrentPage = 3739,     @PageSize = 11,          @Table=N'vDMPrep',     @Field = N'EnglishProductCategoryName,Model,  Age ',     @OrderBy = N'EnglishProductCategoryName',     @Filter = N'Age >40',     @MaxPage = @MaxPage OUTPUT,     @TotalRow = @TotalRow OUTPUT,     @Descript = @Descript OUTPUT

  SELECT @MaxPage as N'@MaxPage',     @TotalRow as N'@TotalRow',     @Descript as N'@Descript'

  SELECT 'Return Value' = @return_value

  SELECT  'Elapsed Time,ms'   =DATEDIFF(ms, @start_time,GETDATE())   GO   GO   */ -- ============================================= ALTER PROCEDURE [dbo].[usp_CommonPage] @CurrentPage INT = 1,      -- 当前页码 @PageSize INT = 10,     -- 每页记录条数(页面大小) @Table NVARCHAR(4000),    -- 表名或视图名，甚至可以是嵌套SQL：(Select * From Tab Where ID>1000) Tab @Field NVARCHAR(4000) = '*',   -- 返回记录集字段名，","隔开，默认是"*" @OrderBy NVARCHAR(100) = 'ID ASC', -- 排序规则 @Filter NVARCHAR(500),    -- 过滤条件 @MaxPage SMALLINT OUTPUT,   -- 执行结果 -1 error, 0 false, maxpage true @TotalRow INT OUTPUT,    -- 记录总数 @Descript VARCHAR(100) OUTPUT  -- 结果描述 AS BEGIN SET ROWCOUNT @PageSize;

SET @Descript = 'successful'; -------------------参数检测---------------- IF LEN(RTRIM(LTRIM(@Table))) !> 0 BEGIN   SET @MaxPage = 0;   SET @Descript = 'Table Name is Empty';   RETURN; END

IF LEN(RTRIM(LTRIM(@OrderBy))) !> 0 BEGIN   SET @MaxPage = 0;   SET @Descript = 'Order is Empty';   RETURN; END

IF ISNULL(@PageSize,0) <= 0 BEGIN   SET @MaxPage = 0;   SET @Descript = 'Page Size Error';   RETURN; END

IF ISNULL(@CurrentPage,0) <= 0 BEGIN   SET @MaxPage = 0;   SET @Descript = 'CurrentPage Error';   RETURN; END -------------------检测结束----------------

BEGIN TRY   -- 整合SQL   DECLARE @SQL NVARCHAR(4000), @SQLCOUNT NVARCHAR(4000), @Portion NVARCHAR(4000)

  --当前页的最小记录号   DECLARE @MinRecordNum INT     --当前页的最大记录号   DECLARE @MaxRecordNum INT     DECLARE @FILTERLEN INT

  SET @MinRecordNum = (@CurrentPage-1)*@PageSize

  SET @MaxRecordNum = (@CurrentPage)*@PageSize;

  SET @Portion = ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') AS ROWNUM FROM ' + @Table;     DECLARE @TableLen INT     SET @TableLen=LEN(@Table)     PRINT @TableLen

  SET @FILTERLEN = LEN(LTRIM(@Filter));   SET @Portion = @Portion + (CASE WHEN @FILTERLEN >= 3 THEN (' WHERE ' + @Filter + ') AS tab') ELSE (') AS tab') END);

  SET @SQL = 'SELECT *  FROM (SELECT ' + @Field + ',' + @Portion;

SET @SQL = @SQL + ' WHERE ROWNUM > ' + CAST(@MinRecordNum AS NVARCHAR(8)) + ' AND ROWNUM <= '+ CAST(@MaxRecordNum AS NVARCHAR(8));

  PRINT(@SQL);   -- 执行SQL, 取当前页记录集   EXECUTE(@SQL);   --------------------------------------------------------------------   SET @Filter = LTRIM(@Filter);   -- 整合SQL   --SET @SQL = 'SET @Rows = (SELECT MAX(ROWNUM) FROM (SELECT' + @Portion + ')';   DECLARE @WHERESTR NVARCHAR(2000)     SET @WHERESTR =  (CASE WHEN LEN(LTRIM(@Filter)) >= 3 THEN (' WHERE ' + @Filter )   ELSE(' ' ) END);     SET @SQLCOUNT = 'SET @TotalRow = (SELECT COUNT(0) FROM '+@Table+ @WHERESTR +')';

  PRINT(@SQLCOUNT);

  -- 执行SQL, 取最大页码   EXECUTE sp_executesql @SQLCOUNT, N'@TotalRow INT OUTPUT', @TotalRow OUTPUT;

  print @TotalRow   SET @MaxPage = (CASE WHEN (@TotalRow % @PageSize)<>0 THEN (@TotalRow / @PageSize + 1) ELSE (@TotalRow / @PageSize) END);   PRINT @MaxPage END TRY BEGIN CATCH   -- 捕捉错误   SET @MaxPage = -1;   SET @Descript = N'Error line: ' + CAST(ERROR_LINE() AS NVARCHAR(8)) + N', Error Number: ' + CAST(ERROR_NUMBER() AS NVARCHAR(8)) + N', Error message: ' + ERROR_MESSAGE();   RETURN; END CATCH;

-- 执行成功 RETURN; END