通用分页的存储过程

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Proc_Util_Page]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Proc_Util_Page
GO

/*
分页
调用方法:EXEC Proc_Util_Page 'country,city', 'table', ' where 1=1', ' order by mid asc', 'mid', 1, 1, 0, ''
输入:
 1.字段(不可为空)
 2.表名(不可为空)
 3.条件(可以为空,需要where)
 4.排序(可以为空,需要order by,需要asc和desc字符)
 5.主键(可以为空)
 6.当前页数
 7.每页记录数
 8.输出总记录条数(若<1则执行count)
 9.输出sql语句
返回:记录集
*/
CREATE PROCEDURE dbo.Proc_Util_Page
(
 @sField nvarchar(1000),
 @sTable nvarchar(1000),
 @sWhere nvarchar(1000),
 @sOrderby nvarchar(1000),
 @sPkey nvarchar(50),
 @iPageIndex int,
 @iPageSize int,
 @iRecordCount int OUTPUT,
 @sOutsql nvarchar(4000) OUTPUT
)
--WITH ENCRYPTION
AS
BEGIN
 SET NOCOUNT ON

 DECLARE @iRC int, @sSQL nvarchar(4000), @sW nvarchar(1000), @sOB nvarchar(1000), @sT nvarchar(100)
 SELECT @iRC = @iRecordCount, @sSQL = '', @sW = ' WHERE 1=1 ', @sOB = ''

 --判断条件
 IF RTRIM(@sWhere) != '' AND @sWhere IS NOT NULL
  BEGIN
   SET @sW=' ' + @sWhere + ' '
  END
 --判断总记录数
 IF @iRC<1
  BEGIN
   SET @sSQL='SELECT @iRC=Count(*) FROM ' + @sTable + @sW
   EXEC sp_executesql @sSQL,N'@iRC int OUT',@iRC OUT
  END

 --判断页数是否超出范围
 SELECT @iPageIndex=(CASE WHEN @iRC<(@iPageIndex-1)*@iPageSize THEN CEILING(@iRC/@iPageSize) WHEN @iPageIndex<1 THEN 1 ELSE @iPageIndex END)

 --判断排序
 IF RTRIM(@sOrderby) != '' AND @sOrderby IS NOT NULL
  BEGIN
   SELECT @sOB=' ' + @sOrderby + ' '
  END

 --如果是第一页
 IF @iPageIndex=1
  BEGIN
   SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sW+@sOB
   GOTO step4
  END

 --看有否主键
 IF RTRIM(@sPkey) = '' OR @sPkey IS NULL
  GOTO step1
 ELSE
  --看是否按主键排序
  BEGIN
   DECLARE @sOB1 nvarchar(1000), @sPkey1 nvarchar(50)
   SELECT @sOB1 = UPPER(@sOrderby), @sPkey1 = UPPER(@sPkey)
   IF CHARINDEX(@sPkey1 + ' ASC', @sOB1)>0
    BEGIN
     SET @sT='>(SELECT MAX('
     GOTO step2
    END
   IF CHARINDEX(@sPkey1 + ' DESC', @sOB1)>0
    BEGIN
     SET @sT='<(SELECT MIN('
     GOTO step2
    END
   GOTO step3
  END

 --如果无主键
 step1:
  BEGIN
   SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sW + ' AND EXISTS (SELECT TOP '+CAST((@iPageIndex-1)*@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sW+@sOB+')'+@sOB+')'
   GOTO step4
  END
 --纯按主键排序
 step2:
  BEGIN
   SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sW+' AND '+@sPkey+@sT+@sPkey+') FROM (SELECT TOP '+CAST((@iPageIndex-1)*@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sW+@sOB+') AS tbTemp)'+@sOB
   GOTO step4
  END
 --不纯按主键排序
 step3:
  BEGIN
   SET @sSQL='SELECT '+@sField+' FROM '+@sTable+@sW + ' AND ' + @sPkey+' IN (SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sW + ' AND ' + @sPkey+' NOT IN(SELECT TOP '+CAST((@iPageIndex-1)*@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sW+@sOB+')'+@sOB+')'+@sOB
   GOTO step4
  END
 --输出最终执行的分页sql语句并执行
 step4:
  SELECT @sOutsql = @sSQL, @iRecordCount = @iRC
  --print(@sSQL)
  EXEC(@sSQL)

 SET NOCOUNT OFF
END
GO