SQL - 分页存储过程
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 @TotalCount END DECLARE @return_value int, @TotalCount int EXEC @return_value = [dbo].[Sp_LeePageProc] @tbName = N'T002_StoreInfo', @tbGetFields = N'*', @OrderfldName = N'代码', @PageSize = 20, @PageIndex = 1, @OrderType = 0, @TotalCount = @TotalCount OUTPUT