总结的几个 sql 分页存储过程

 

06	CREATE PROCEDURE [dbo].[sp_All_Pager]

07	@tblName nvarchar(255),--表名

08	@fldNames nvarchar(1000)='*',--字段列表(默认所有字段)

09	@fldOrderName nvarchar(255),--主键字段(排序用)

10	@orderType bit =0,--排序类型,非0则降序(默认升序)

11	@pageIndex int=1,--页码

12	@pageSize int =20,--页大小

13	@strWhere nvarchar(1500)='',--查询条件,不带where

14	@limitCount int =5000, --限制最多取5000条数据

15	@totalCount int output --总记录数

16

AS

17

BEGIN

18	SET NOCOUNT ON;

19	declare @strSQL nvarchar(4000)--主语句

20	declare @strTable nvarchar(500)--主查询临时表

21	declare @strOrderSQL nvarchar(1000)--总数查询语句

22	declare @strOutParam nvarchar(500)--输出参数

23	declare @strTmp nvarchar(255)--临时分页字句 max 和min

24	declare @strOrder nvarchar(255)--排序参数

25

26

--设置排序

27	if(@orderType != 0)

28

begin

29	set @strTmp = ' <(select min '

30	set @strOrder = ' order by [' + @fldOrderName +'] desc'

31

end

32

else

33

begin

34	set @strTmp = ' >(select max '

35	set @strOrder = ' order by [' + @fldOrderName +'] asc'

36

end

37	--设置主查询临时表

38	if(@strWhere!='')

39	set @strTable='(select top '+STR(@limitCount)+' '+@fldNames+' from ['+@tblName+'] where '+@strWhere+') as tmpTable'

40

else

41	set @strTable='(select top '+STR(@limitCount)+' '+@fldNames+' from ['+@tblName+']) as tmpTable'

42	--设置总数查询语句和主语句

43	set @strOrderSQL=N'select @totalCountOut=count(1) from '+@strTable

44	set @strOutParam=N'@totalCountOut int output'

45	if (@pageIndex = 1)

46	set @strSQL = 'select top ' + str(@pageSize) +' '+@fldNames+ ' from ' + @strTable + @strOrder

47

else

48	set @strSQL = 'select top ' + str(@PageSize) +' '+@fldNames+ ' from ' + @strTable + ' where [' + @fldOrderName + ']' + @strTmp + '(['+ @fldOrderName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldOrderName + '] from ' + @strTable + @strOrder + ') as tblTmp) ' + @strOrder

49	exec sp_executesql @strOrderSQL,@strOutParam,@totalCountOut=@totalCount output

50	exec sp_executesql @strSQL

51

END

52

53

GO

 

 

 

 

 

 

 

 

---------------------------------------------------------------------------------------------------------

 

 

 

 

 

 

 

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE proc [ dbo ] . [ up_Page2005 ] @TableName varchar ( 50 ), -- 表名 @Fields varchar ( 5000 ) = ' * ' , -- 字段名(全部字段为*) @OrderField varchar ( 5000 ), -- 排序字段(必须!支持多字段) @sqlWhere varchar ( 5000 ) = Null , -- 条件语句(不用加where) @pageSize int , -- 每页多少条记录 @pageIndex int = 1 , -- 指定当前为第几页 @TotalPage int output -- 返回总页数 as begin Begin Tran -- 开始事务 Declare @sql nvarchar ( 4000 ); Declare @totalRecord int ; -- 计算总记录数 if ( @SqlWhere = '' or @sqlWhere = NULL ) set @sql = ' select @totalRecord = count(*) from ' + @TableName else set @sql = ' select @totalRecord = count(*) from ' + @TableName + ' with(nolock) where ' + @sqlWhere EXEC sp_executesql @sql ,N ' @totalRecord int OUTPUT ' , @totalRecord OUTPUT -- 计算总记录数 -- 计算总页数 select @TotalPage = CEILING (( @totalRecord + 0.0 ) / @PageSize ) if ( @SqlWhere = '' or @sqlWhere = NULL ) set @sql = ' Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ' ) as rowId, ' + @Fields + ' from ' + @TableName else set @sql = ' Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ' ) as rowId, ' + @Fields + ' from ' + @TableName + ' with(nolock) where ' + @SqlWhere -- 处理页数超出范围情况 if @PageIndex <= 0 Set @pageIndex = 1 if @pageIndex > @TotalPage Set @pageIndex = @TotalPage -- 处理开始点和结束点 Declare @StartRecord int Declare @EndRecord int set @StartRecord = ( @pageIndex - 1 ) * @PageSize + 1 set @EndRecord = @StartRecord + @pageSize - 1 -- 继续合成sql语句 set @Sql = @Sql + ' ) as t where rowId between ' + Convert ( varchar ( 50 ), @StartRecord ) + ' and ' + Convert ( varchar ( 50 ), @EndRecord ) print @sql Exec ( @Sql ) -- ------------------------------------------------- If @@Error <> 0 Begin RollBack Tran Return - 1 End Else Begin Commit Tran Return @totalRecord -- -返回记录总数 End end

 

 

 

 

------------------------------------------------------------------------------------------------------------------

 

 

 

 

SET  QUOTED_IDENTIFIER  OFF  
GO
SET  ANSI_NULLS  ON  
GO
-- 名称：分页存储过程
-- 使用示例 EXEC sp_PageIndex '*',' FROM StuSources ',2,10
-- 注意 
-- 目前还没有对输入的参数进行严格的验证
-- 默认为输入都是合法有效的

ALTER    PROC  sp_PageIndex
  @sqlSelect   varchar ( 800 )  -- SELECT 后面 FROM 前面 的 字段 不用包含SELECT
, @sqlFrom   varchar ( 800 )  -- FROM 后面 的 字段 包含FROM
, @countPerPage   int   --  每页数据行数
, @toPage   int   -- 要转到的页码

AS

BEGIN


--  根据每页数据行数 和 要转到的页码 得到 数据起止点
Declare   @start   int
Declare   @end   int

set   @end   =   @countPerPage   *   @toPage
set   @start   =   @countPerPage   *  ( @toPage   -   1 )  +   1


--  临时表名称 可随机命名
Declare   @tmpTable   varchar ( 10 )
SET   @tmpTable   = ' #tmp '

Declare   @sqlStr   varchar ( 800 )
--  创建数据源到临时表
SELECT   @sqlStr   =   ' SELECT Identity(int,1,1) AS RowIndex, '
SELECT   @sqlStr   =   @sqlStr   +   rtrim ( @sqlSelect )  +   '  INTO   ' +   @tmpTable  
SELECT   @sqlStr   =   @sqlStr   +   rtrim ( @sqlFrom ) 
--  查询临时表 得到所需要的数据
SELECT   @sqlStr   =   @sqlStr   +   '   ' + ' SELECT  ' +   rtrim ( @sqlSelect )  + '  FROM  '   +   @tmpTable  
SELECT   @sqlStr   =   @sqlStr   +   '  WHERE  RowIndex BETWEEN  '   +   Convert ( char , @start )  +  "  AND  "  +   Convert ( char , @end )
--  删除临时表
SELECT   @sqlStr   =   @sqlStr   +   '   ' + ' DROP TABLE  ' + @tmpTable
EXEC  ( @sqlStr )


END


GO
SET  QUOTED_IDENTIFIER  OFF  
GO
SET  ANSI_NULLS  ON  
GO