一个项目中正在使用的SQL分页存储过程

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

Create PROCEDURE [dbo].[Common_Pagination]

@tablename varchar(100),
@tablefield varchar(20),
@where varchar(5000),
@orderby varchar(500),
@fieldlist varchar(1000),
@curpage int,
@page_record int,
@sort varchar(8)

AS

BEGIN

  DECLARE @cmd varchar(8000)
  DECLARE @uprecord int
  DECLARE @Op varchar(2) -- 操作符
  DECLARE @max_min varchar(4) -- 最大/最小计算

  SET @op = '<'
  SET @max_min = 'MIN'

  IF @sort = 'asc'
      BEGIN
      SET @Op = '>'
            SET @max_min = 'MAX'
      END

   SET @uprecord=@curpage * @page_record
   SET @where = '('+@where+')'

   IF @curpage = 0
        SET @cmd = 'SELECT TOP '+cast(@page_record AS NVARCHAR)+' '+@fieldlist+' FROM '+@tablename+' WHERE '+@where+' '+@orderby
   ELSE
     SET @cmd = 'SELECT TOP '+cast(@page_record AS NVARCHAR)+' '+@fieldlist+' FROM '+@tablename+' WHERE '+@where+' AND '+@tablefield+'
     '+@op+' (SELECT '+@max_min+'('+@tablefield+')  FROM (SELECT TOP '+cast(@uprecord AS NVARCHAR)+' '+@tablefield+' FROM '+@tablename+' WHERE
     '+@where+' '+@orderby+') AS TmpTbl ) AND '+@where+' '+@orderby

   EXEC(@cmd)
   PRINT(@cmd)

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

转载于:https://www.cnblogs.com/bccu/archive/2008/01/30/1059063.html