分页存储过程

分页存储过程

涉及sql关键字：

nocount：阻止在结果集中返回可显示受 Transact-SQL 语句或存储过程影响的行计数的消息。

当 SET NOCOUNT 为 ON 时，不返回计数（表示受 Transact-SQL 语句影响的行数）。当 SET NOCOUNT 为 OFF 时，返回计数。

如果存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。

execute(exec):执行 Transact-SQL 批处理中的命令字符串、字符串或执行下列模块之一：系统存储过程、用户定义存储过程、CLR 存储过程、标量值用户定义函数或扩展存储过程。

case when:相当于if，使用方式：

case result
     when '胜' then 1
     when '负' then 2
else 0

第二种方式：

case when result='胜' then 1
         when result='负' then 2
else  0

max函数:

必须配合as使用例如：

select max(id) from (select top 5000 id from yy_detail order by id )as TempTable

其中少了as max不能使用

原因max必须在一个独立的集合中使用

Row_number：ROW_NUMBER() OVER(ORDER BY orderID DESC) AS 字段编号。给数据行加上索引，并以此分页

sp_executesql ：执行可多次重复使用或动态生成的 Transact-SQL 语句或批处理。 Transact-SQL 语句或批处理可以包含嵌入参数。用法：EXECUTE sp_executesql @tempCount,N'@recordTotal INT OUTPUT',@recordTotal OUTPUT结构为sp_executesql ，参数定义，参数，输出参数，在此参数省略了.加 N 就表示字符串用 Unicode 方式存储。

1.not in的方式

ALTER procedure [dbo].[pro_fenye]
@pagesize int, --页索引
@pagenow int   --每页记录数
as 
begin 
set nocount on;

declare @sql nvarchar(500)


set @sql='select top '+str(@pagesize)+'* from yy_detail where id not in (
select top '+str(@pagesize*(@pagenow-1))+' id from yy_detail) '
execute (@sql)

set nocount off;


end

 

2.top和max的组合方式

set nocount on; 
   declare @sql nvarchar(500) 
   select @timediff=Getdate() 
   set @sql='select top '+str(@pageSize)+' * From tb_TestTable where(ID>(select max(id) From (select top '+str(@pageSize*@pageIndex)+' id From tb_TestTable order by ID) as TempTable)) order by ID' 
   execute(@sql) 
set nocount off; 

 

3.sql server 2005之后的版本使用Row_number()

set nocount on; 
select * from (select *,Row_number() over(order by ID asc) as IDRank from tb_testTable) as IDWithRowNumber where IDRank>@pageSize*@pageIndex and IDRank<@pageSize*(@pageIndex+1) 
set nocount off; 

 

4.临时表和中间量

  set nocount on; 
    declare @ctestr nvarchar(400) 
    declare @strSql nvarchar(400) 

begin 
    select @datediff=GetDate() 
　　　--其中CTE必须紧跟使用它的主语句
    set @ctestr='with Table_CTE as 
                (select ceiling((Row_number() over(order by ID ASC))/'+str(@pageSize)+') as page_num,* from tb_TestTable)'; 
    set @strSql=@ctestr+' select * From Table_CTE where page_num='+str(@pageIndex) 
end 
    begin 
        execute sp_executesql @strSql 
       
    set nocount off; 

 

总结：二分法结合max做出的分页存储过程

CREATE PROCEDURE [dbo].[P_GridViewPager] (
    @recordTotal INT OUTPUT,            --输出记录总数
    @viewName VARCHAR(800),        --表名
    @fieldName VARCHAR(800) = '*',        --查询字段
    @keyName VARCHAR(200) = 'Id',            --索引字段
    @pageSize INT = 20,                    --每页记录数
    @pageNo INT =1,                    --当前页
    @orderString VARCHAR(200),        --排序条件
    @whereString VARCHAR(800) = '1=1'        --WHERE条件
)
AS
BEGIN
     DECLARE @beginRow INT 
　　　--开始行与结束行
     DECLARE @endRow INT
　　　--查询条件：数据的位置
     DECLARE @tempLimit VARCHAR(200)
     --输出已查询的总行数
     DECLARE @tempCount NVARCHAR(1000)
　　　--主查询语句
     DECLARE @tempMain VARCHAR(1000)
     --declare @timediff datetime 
     
     set nocount on
     --select @timediff=getdate() --记录时间

     SET @beginRow = (@pageNo - 1) * @pageSize    + 1
     SET @endRow = @pageNo * @pageSize
     SET @tempLimit = 'rows BETWEEN ' + CAST(@beginRow AS VARCHAR) +' AND '+CAST(@endRow AS VARCHAR)
     
     --输出参数为总记录数
     SET @tempCount = 'SELECT @recordTotal = COUNT(*) FROM (SELECT '+@keyName+' FROM '+@viewName+' WHERE '+@whereString+') AS my_temp'
     --sp_executesql 下面参数：执行语句，Unicode 常量的参数定义，输入参数（此处无）,输出参数
     EXECUTE sp_executesql @tempCount,N'@recordTotal INT OUTPUT',@recordTotal OUTPUT
       
     --主查询返回结果集
     SET @tempMain = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (order by '+@orderString+') AS rows ,'+@fieldName+' FROM '+@viewName+' WHERE '+@whereString+') AS main_temp WHERE '+@tempLimit
     
     --PRINT @tempMain
     EXECUTE (@tempMain)
     --select datediff(ms,@timediff,getdate()) as 耗时 
     
     set nocount off
END

GO

 参考博客：http://www.cnblogs.com/lli0077/archive/2008/09/03/1282862.html