分页存储过程（单表实例）

USE [SxbFanWen]
GO
/****** Object:  StoredProcedure [dbo].[usp_FW_KeyWord_Pager]    Script Date: 07/14/2015 16:43:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[usp_FW_KeyWord_Pager]
@pageindex int,
@pagesize int,
@rowcount int output,
@where nvarchar(400)
as
declare @sql nvarchar(max);
declare @pagecount int;
set @sql=' select @rowcount=count(1) from FW_KeyWord '+@where;
--exec @sql @rowcount=@rowcount output;
exec sp_executesql @sql, N'@rowcount int out', @rowcount out

if @rowcount%@pagesize=0 
 set @pagecount=@rowcount/@pagesize;
 else
	set @pagecount=@rowcount/@pagesize+1;
	
if @pageindex>@pagecount 
	set @pageindex=@pagecount;

set @sql='select a.* from (
SELECT     FW_KeyWord.Kid, FW_KeyWord.keywordName, FW_KeyWord.Title, FW_KeyWord.KeyWord, FW_KeyWord.Description, FW_KeyWord.cid, FW_KeyWord.sid, 
                      FW_KeyWord.tid, FW_KeyWord.sort, FW_KeyWord.mark, FW_Class.CName, FW_SubClass.sName, FW_ThreeClass.tname,row_number() over (order by FW_KeyWord.sort,FW_KeyWord.Kid) rowno
FROM         FW_KeyWord INNER JOIN
                      FW_Class ON FW_KeyWord.cid = FW_Class.CID LEFT OUTER JOIN
                      FW_SubClass ON FW_KeyWord.sid = FW_SubClass.SID LEFT OUTER JOIN
                      FW_ThreeClass ON FW_KeyWord.tid = FW_ThreeClass.tid'+@where+')a where a.rowno between '
 set @sql=@sql+cast( (@pageindex-1)*@pagesize+1 as nvarchar)+' and '+ convert( nvarchar, @pageindex*@pagesize);                  
                      exec(@sql);