SQLSERVER 分页存储过程(2 在SQL2005下使用)

 

调用 带参数存储过程

declare @totalRec int
exec fenye 'venshop_hw',20,1,'hw_id desc','hw_id,hw_name,hw_sn,hw_price1,hw_price2,hw_kucun','sort_id=1',@totalRec output
print '总计'+convert(varchar(6),@totalRec)+'条'

 

 

/**
if  exists(select * from sysobjects where name='fenye')
drop proc fenye
**/

 

CREATE procedure fenye
  @tableName nvarchar(200) , --表名
  @pageSize int,   --每页显示条数
  @curPage int ,  --当前页
  @orderBy nvarchar(200) ,   --排序字段 
  @field nvarchar(200) = '*' ,  --要查询的字段
  @condition nvarchar(200),  --条件（不用写where）
  @recct int output   --返回记录总数（输出参数）
AS
  SET NOCOUNT ON
  DECLARE
     @STMT nvarchar(max)        -- SQL to execute
     --@recct int                  -- total # of records (for GridView paging interface)
  IF LTRIM(RTRIM(@condition)) = '' SET @condition = '1 = 1'
  IF @pageSize IS NULL BEGIN
    SET @STMT =  'SELECT   ' + @field + 'FROM ' + @tableName +'WHERE    ' + @condition + 'ORDER BY   ' + @orderBy
    EXEC (@STMT)                 -- return requested records
  END ELSE BEGIN
    SET @STMT =  'SELECT   @recct = COUNT(*) FROM     ' + @tableName + '    WHERE    ' + @condition
    EXEC sp_executeSQL @STMT, @params = N'@recct INT OUTPUT', @recct = @recct OUTPUT
    --SELECT @recct AS recct       -- return the total # of records
    DECLARE
      @lbound int,
      @ubound int

    SET @curPage = ABS(@curPage)
    SET @pageSize = ABS(@pageSize)
    IF @curPage < 1 SET @curPage = 1
    IF @pageSize < 1 SET @pageSize = 1
    SET @lbound = ((@curPage - 1) * @pageSize)
    SET @ubound = @lbound + @pageSize + 1
    IF @lbound >= @recct BEGIN
      SET @ubound = @recct + 1
      SET @lbound = @ubound - (@pageSize + 1)
    END
    SET @STMT =  'SELECT  ' + @field + '
                  FROM    (
                            SELECT  ROW_NUMBER() OVER(ORDER BY ' + @orderBy + ') AS row, '+  @field +'
                            FROM    ' + @tableName + '
                            WHERE   ' + @condition + '
                          ) AS tbl
                  WHERE
                          row > ' + CONVERT(varchar(9), @lbound) + ' AND
                          row < ' + CONVERT(varchar(9), @ubound)
    --EXEC  sp_executeSQL @STMT                 -- return requested records

 EXEC  sp_executeSQL @STMT
  END