【SQL】存储过程分页代码详解

代码部分过程部分:

CREATE procedure [dbo].[WS_GetKbDoc]
@KBTypeID int,
@Keyword nvarchar(50),
@pagesize int,
@pageindex int
as
begin
 with temptbl as (
  SELECT ROW_NUMBER() OVER (ORDER BY d.CreatedDate desc)AS RowNum, d.*,s.StaffName,s.StaffNo,k.KBTypeName
  from dbo.WS_Document d
  left join dbo.WS_Staff s on s.UserID=d.CreatedBy
  left join dbo.WS_KnowledgeBaseType k on k.KBTypeID=d.KBTypeID
  where d.active=1 and IsKnowledgeBase=1 
  and (@KBTypeID=0 or d.KBTypeID=@KBTypeID)
  and (@Keyword='' or d.DocName LIKE '%' + @Keyword + '%' or d.DocDetail LIKE '%' + @Keyword + '%' or d.DocExtension LIKE '%' + @Keyword + '%' or s.StaffName LIKE '%' + @Keyword + '%')
 )

  SELECT * FROM temptbl where RowNum between (@pageindex-1)*@pagesize+1 and (@pageindex-1)*@pagesize+@pagesize
  
  select count(*) from dbo.WS_Document d
  left join dbo.WS_Staff s on s.UserID=d.CreatedBy
  where d.active=1 and IsKnowledgeBase=1 
  and (@KBTypeID=0 or d.KBTypeID=@KBTypeID)
  and (@Keyword='' or d.DocName LIKE '%' + @Keyword + '%' or d.DocDetail LIKE '%' + @Keyword + '%' or d.DocExtension LIKE '%' + @Keyword + '%' or s.StaffName LIKE '%' + @Keyword + '%')
end

定义说明:

@KBTypeID int, 类型标识
@Keyword nvarchar(50),搜索关键字
@pagesize int,每一页的大小
@pageindex int,传进业的索引

SQL说明:

With:With 通常与AS连用，也叫做子查询部分

　     1).   可用来定义一个SQL片断，该片断会被整个SQL语句所用到。
        2).   为了让SQL语句的可读性更高
        3).   也有可能是在UNION ALL的不同部分，作为提供数据的部分。特别对于UNION ALL比较有
   　　      用。因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本
       　     太高，所以可以使用WITH AS短语，则只要执行一遍即可。

ROW_NUMBER:返回结果集分区内行的序列号，每个分区的第一行从 1 开始。 http://msdn.microsoft.com/zh-cn/library/ms186734.aspx

详解:

with temptbl as (
    SELECT ROW_NUMBER() OVER (ORDER BY d.CreatedDate desc)AS RowNum, d.*,s.StaffName,s.StaffNo,k.KBTypeName
from dbo.WS_Document d
left join dbo.WS_Staff s on s.UserID=d.CreatedBy
left join dbo.WS_KnowledgeBaseType k on k.KBTypeID=d.KBTypeID
where d.active=1 and IsKnowledgeBase=1 
and (@KBTypeID=0 or d.KBTypeID=@KBTypeID)
and (@Keyword='' or d.DocName LIKE '%' + @Keyword + '%' or d.DocDetail LIKE '%' + @Keyword + '%' or d.DocExtension LIKE '%' + @Keyword + '%' or s.StaffName LIKE '%' + @Keyword + '%')
    )

这段代码的主要是搜索需要的数据然后进行编号

SELECT * FROM temptbl 
where RowNum between 
(@pageindex-1)*@pagesize+1 
and 
(@pageindex-1)*@pagesize+@pagesize

这段代码的主要是把当面检索到得数据库，通过@pageindex和@pagesize索引页来获取请求业的数据,如页大小10,第一页即为行号0到10,第二页为11到20

select count(*) from 
dbo.WS_Document d
left join dbo.WS_Staff s on s.UserID=d.CreatedBy
where d.active=1 and IsKnowledgeBase=1 
and (@KBTypeID=0 or d.KBTypeID=@KBTypeID)
and (@Keyword='' or d.DocName LIKE '%' + @Keyword + '%' 
or d.DocDetail LIKE '%' + @Keyword + '%' 
or d.DocExtension LIKE '%' + @Keyword + '%' 
or s.StaffName LIKE '%' + @Keyword + '%')

这段代码是获取总共符合条件的数据的条数.

上面存储过程用DataSet接受可以得到两张表。

在前台可以通过Table[1]来进行访问总数据