http://www.jb51.net/article/71193.htm

http://www.webdiyer.com/utils/spgenerator/

create PROCEDURE [dbo].[Proc_SqlPageByRownumber]( @tbName VARCHAR(255),   --表名 @tbGetFields VARCHAR(1000)= '*',--返回字段 @OrderfldName VARCHAR(255),  --排序的字段名 @PageSize INT=20,    --页尺寸 @PageIndex INT=1,    --页码 @OrderType bit = 0,    --0升序，非0降序 @strWhere VARCHAR(1000)='',  --查询条件 @TotalCount INT OUTPUT   --返回总记录数)AS-- =============================================-- Author:  allen (liyuxin)-- Create date: 2012-03-30-- Description: 分页存储过程(支持多表连接查询)-- Modify [1]: 2012-03-30-- =============================================BEGIN DECLARE @strSql VARCHAR(5000) --主语句 DECLARE @strSqlCount NVARCHAR(500)--查询记录总数主语句 DECLARE @strOrder VARCHAR(300) -- 排序类型 --------------总记录数--------------- IF ISNULL(@strWhere,'') <>''   SET @strSqlCount='Select @TotalCout=count(*) from ' + @tbName + ' where 1=1 '+ @strWhere ELSE SET @strSqlCount='Select @TotalCout=count(*) from ' + @tbName exec sp_executesql @strSqlCount,N'@TotalCout int output',@TotalCount output --------------分页------------ IF @PageIndex <= 0 SET @PageIndex = 1 IF(@OrderType<>0) SET @strOrder=' ORDER BY '+@OrderfldName+' DESC ' ELSE SET @strOrder=' ORDER BY '+@OrderfldName+' ASC ' SET @strSql='SELECT * FROM  (SELECT ROW_NUMBER() OVER('+@strOrder+') RowNo,'+ @tbGetFields+' FROM ' + @tbName + ' WHERE 1=1 ' + @strWhere+' ) tb  WHERE tb.RowNo BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND ' +str(@PageIndex*@PageSize) exec(@strSql) SELECT @TotalCountENDDECLARE    @return_value int,        @TotalCount intEXEC    @return_value = [dbo].[Sp_LeePageProc]        @tbName = N'T002_StoreInfo',        @tbGetFields = N'*',        @OrderfldName = N'代码',        @PageSize = 20,        @PageIndex = 1,        @OrderType = 0,        @TotalCount = @TotalCount OUTPUT