一个实现分页的存储过程实例代码

USE [database]
GO
/****** Object:  StoredProcedure [dbo].[PR_Page]    Script Date: 03/04/2009 12:45:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PR_Page]
@tableName varchar(255),
@fldName varchar(255),
@strGetFields varchar(1000) = ' * ',
@PageSize int = 10,
@PageIndex int = 1,
@Count int output,
@OrderType bit = 0,
@strWhere varchar(1500) = ' 1=1 '

AS

DECLARE @strSQL varchar(5000)
DECLARE @strTmp varchar(110)
DECLARE @strOrder varchar(400)
DECLARE @sumsql nvarchar(3000)

SET @sumsql = 'select @Count = count(*) from '+ @tableName +' where '+ @strwhere
exec sp_executesql @sumsql, N'@Count int output', @Count output

IF(@OrderType != 0)
 BEGIN
  SET @strTmp = '<(select min'
  SET @strOrder = ' order by ['+ @fldName +'] desc'
 END 
ELSE
 BEGIN
  SET @strTmp = '>(select max'
  SET @strOrder = ' order by ['+ @fldName +'] asc'
 END 

IF @PageIndex = 1
 SET @strSQL = 'select top '+ str(@PageSize) +' '+ @strGetFields +' from ['+ @tableName +'] where '+ @strWhere +' '+ @strOrder
 
ELSE
 SET @strSQL = 'select top '+ str(@PageSize) +' '+ @strGetFields +' from ['+ @tableName +'] where ['+ @fldName +'] '+ @strTmp +' (['+ @fldName +']) from (select top '+ str((@PageIndex - 1)*@PageSize) +' ['+ @fldName +'] from ['+ @tableName +'] where '+ @strWhere +' '+ @strOrder +') as tableTmp) and '+ @strWhere +' '+ @strOrder

exec(@strSQL);