![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
/*
***** 对象: StoredProcedure [dbo].[p_generalTablePage] 脚本日期: 12/30/2009 13:25:02 *****
*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: dean
-- Create date: 2008-06-26
-- Description: page
-- =============================================
CREATE PROCEDURE [ dbo ] . [ p_generalTablePage ]
@strTbName varchar ( 400 ) -- 表名
, @strFeilds varchar ( 400 ) -- 显示字段
, @strOrder varchar ( 200 ) -- 排序字段
, @strWhere varchar ( 400 ) -- 查询条件 (注意: 不要加where)
, @PageSize int = 15 -- 页尺寸
, @PageIndex int = 1 -- 当前页码
, @masterFeilds varchar ( 50 ) -- 主关键字
, @OrderType int = 0 -- 设置排序类型, 非 0 值则降序
, @RecordCount int output -- 返回记录数
, @totalPage int output -- 返回总页数
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON ;
DECLARE @strSQLCnt nvarchar ( 3000 ) -- 统计语句
, @strSQL varchar ( 5000 ) -- 主语句
IF @OrderType != 0
BEGIN
if @strOrder != '' And @strOrder is not null
set @strOrder = ' order by ' + @strOrder + ' , ' + @masterFeilds + ' desc '
else
set @strOrder = ' order by ' + @masterFeilds + ' desc '
END
ELSE
BEGIN
if @strOrder != '' And @strOrder is not null
set @strOrder = ' order by ' + @strOrder + ' , ' + @masterFeilds + ' asc '
else
set @strOrder = ' order by ' + @masterFeilds + ' asc '
END
IF @strFeilds = '' or @strFeilds is Null
SET @strFeilds = ' * '
IF @strWhere != ''
SET @strSQLCnt = ' SELECT @RecordCount = COUNT(*) FROM (SELECT ' + @strFeilds + ' FROM ' + @strTbName + ' WHERE ' + @strWhere + ' ) AS tmpWebListTable '
ELSE
SET @strSQLCnt = ' SELECT @RecordCount = COUNT(*) FROM ' + @strTbName
EXEC sp_executesql @strSQLCnt ,N ' @RecordCount int output ' , @RecordCount output
SET @totalPage = dbo.MyTopInt( @RecordCount , @PageSize )
IF @PageIndex > @totalPage
SET @PageIndex = @totalPage
IF @PageIndex < 1
SET @PageIndex = 1
-- Insert statements for procedure here
IF @strWhere != '' And @strWhere is not null
SET @strSQL = ' select * from (select ' + @strFeilds + ' ,row_number() over ( ' + @strOrder + ' ) AS row from ' + @strTbName + ' WHERE ' + @strWhere + ' ) deanTmpTable where row between ' + ltrim ( str ( @PageSize * ( @PageIndex - 1 ) + 1 )) + ' and ' + ltrim ( str ( @PageSize * @PageIndex ))
ELSE
SET @strSQL = ' select * from (select ' + @strFeilds + ' ,row_number() over ( ' + @strOrder + ' ) AS row from ' + @strTbName + ' ) deanTmpTable where row between ' + ltrim ( str ( @PageSize * ( @PageIndex - 1 ) + 1 )) + ' and ' + ltrim ( str ( @PageSize * @PageIndex ))
-- PRINT @RecordCount
PRINT ( @strSQL )
EXEC ( @strSQL )
END
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: dean
-- Create date: 2008-06-26
-- Description: page
-- =============================================
CREATE PROCEDURE [ dbo ] . [ p_generalTablePage ]
@strTbName varchar ( 400 ) -- 表名
, @strFeilds varchar ( 400 ) -- 显示字段
, @strOrder varchar ( 200 ) -- 排序字段
, @strWhere varchar ( 400 ) -- 查询条件 (注意: 不要加where)
, @PageSize int = 15 -- 页尺寸
, @PageIndex int = 1 -- 当前页码
, @masterFeilds varchar ( 50 ) -- 主关键字
, @OrderType int = 0 -- 设置排序类型, 非 0 值则降序
, @RecordCount int output -- 返回记录数
, @totalPage int output -- 返回总页数
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON ;
DECLARE @strSQLCnt nvarchar ( 3000 ) -- 统计语句
, @strSQL varchar ( 5000 ) -- 主语句
IF @OrderType != 0
BEGIN
if @strOrder != '' And @strOrder is not null
set @strOrder = ' order by ' + @strOrder + ' , ' + @masterFeilds + ' desc '
else
set @strOrder = ' order by ' + @masterFeilds + ' desc '
END
ELSE
BEGIN
if @strOrder != '' And @strOrder is not null
set @strOrder = ' order by ' + @strOrder + ' , ' + @masterFeilds + ' asc '
else
set @strOrder = ' order by ' + @masterFeilds + ' asc '
END
IF @strFeilds = '' or @strFeilds is Null
SET @strFeilds = ' * '
IF @strWhere != ''
SET @strSQLCnt = ' SELECT @RecordCount = COUNT(*) FROM (SELECT ' + @strFeilds + ' FROM ' + @strTbName + ' WHERE ' + @strWhere + ' ) AS tmpWebListTable '
ELSE
SET @strSQLCnt = ' SELECT @RecordCount = COUNT(*) FROM ' + @strTbName
EXEC sp_executesql @strSQLCnt ,N ' @RecordCount int output ' , @RecordCount output
SET @totalPage = dbo.MyTopInt( @RecordCount , @PageSize )
IF @PageIndex > @totalPage
SET @PageIndex = @totalPage
IF @PageIndex < 1
SET @PageIndex = 1
-- Insert statements for procedure here
IF @strWhere != '' And @strWhere is not null
SET @strSQL = ' select * from (select ' + @strFeilds + ' ,row_number() over ( ' + @strOrder + ' ) AS row from ' + @strTbName + ' WHERE ' + @strWhere + ' ) deanTmpTable where row between ' + ltrim ( str ( @PageSize * ( @PageIndex - 1 ) + 1 )) + ' and ' + ltrim ( str ( @PageSize * @PageIndex ))
ELSE
SET @strSQL = ' select * from (select ' + @strFeilds + ' ,row_number() over ( ' + @strOrder + ' ) AS row from ' + @strTbName + ' ) deanTmpTable where row between ' + ltrim ( str ( @PageSize * ( @PageIndex - 1 ) + 1 )) + ' and ' + ltrim ( str ( @PageSize * @PageIndex ))
-- PRINT @RecordCount
PRINT ( @strSQL )
EXEC ( @strSQL )
END
:P
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
/* ***** 对象: UserDefinedFunction [dbo].[MyTopInt] 脚本日期: 12/30/2009 13:12:38 ***** */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: dean
-- Create date: < Create Date, , >
-- Description: 计算总页数
-- =============================================
CREATE FUNCTION [dbo].[MyTopInt]
(
@tRs int ,
@ps int
)
RETURNS int
AS
BEGIN
DECLARE @t1 int
IF @tRs > 0 AND @ps > 0
BEGIN
IF @tRs % @ps = 0
SET @t1 = FLOOR(@tRs / @ps)
ELSE
SET @t1 = FLOOR(@tRs / @ps) + 1
END
ELSE
SET @t1 = 0
RETURN @t1
END
这是我的分页存储过程,应用过几个项目了,也改过很多次,支持子查询或联表查询。特发出来交流一下,大家请P。
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
DECLARE
@return_value
int
,
@RecordCount int ,
@totalPage int
EXEC @return_value = [ dbo ] . [ p_generalTablePage ]
@strTbName = N ' webTable ' ,
@strFeilds = NULL ,
@strOrder = NULL ,
@strWhere = NULL ,
@PageSize = 20 ,
@PageIndex = 1 ,
@masterFeilds = N ' wid ' ,
@OrderType = 1 ,
@RecordCount = @RecordCount OUTPUT,
@totalPage = @totalPage OUTPUT
SELECT @RecordCount as N ' @RecordCount ' ,
@totalPage as N ' @totalPage '
SELECT ' Return Value ' = @return_value
GO
@RecordCount int ,
@totalPage int
EXEC @return_value = [ dbo ] . [ p_generalTablePage ]
@strTbName = N ' webTable ' ,
@strFeilds = NULL ,
@strOrder = NULL ,
@strWhere = NULL ,
@PageSize = 20 ,
@PageIndex = 1 ,
@masterFeilds = N ' wid ' ,
@OrderType = 1 ,
@RecordCount = @RecordCount OUTPUT,
@totalPage = @totalPage OUTPUT
SELECT @RecordCount as N ' @RecordCount ' ,
@totalPage as N ' @totalPage '
SELECT ' Return Value ' = @return_value
GO
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
DECLARE
@return_value
int
,
@RecordCount int ,
@totalPage int
EXEC @return_value = [ dbo ] . [ p_generalTablePage ]
@strTbName = N ' webTable ' ,
@strFeilds = N ' wid,shortName,url,resume ' ,
@strOrder = N ' City desc,shortname desc ' ,
@strWhere = N ' siteid=34 ' ,
@PageSize = 20 ,
@PageIndex = 1 ,
@masterFeilds = N ' wid ' ,
@OrderType = 1 ,
@RecordCount = @RecordCount OUTPUT,
@totalPage = @totalPage OUTPUT
SELECT @RecordCount as N ' @RecordCount ' ,
@totalPage as N ' @totalPage '
SELECT ' Return Value ' = @return_value
GO
@RecordCount int ,
@totalPage int
EXEC @return_value = [ dbo ] . [ p_generalTablePage ]
@strTbName = N ' webTable ' ,
@strFeilds = N ' wid,shortName,url,resume ' ,
@strOrder = N ' City desc,shortname desc ' ,
@strWhere = N ' siteid=34 ' ,
@PageSize = 20 ,
@PageIndex = 1 ,
@masterFeilds = N ' wid ' ,
@OrderType = 1 ,
@RecordCount = @RecordCount OUTPUT,
@totalPage = @totalPage OUTPUT
SELECT @RecordCount as N ' @RecordCount ' ,
@totalPage as N ' @totalPage '
SELECT ' Return Value ' = @return_value
GO
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
DECLARE @return_value int ,
@RecordCount int ,
@totalPage int
EXEC @return_value = [ dbo ] . [ p_generalTablePage ]
@strTbName = N ' webTable inner join SubSite on webTable.siteid = SubSite.ssid ' ,
@strFeilds = N ' webTable.wid,webTable.shortName,webTable.url,webTable.resume ' ,
@strOrder = N ' City desc,shortname desc ' ,
@strWhere = N ' siteid=34 ' ,
@PageSize = 20 ,
@PageIndex = 1 ,
@masterFeilds = N ' wid ' ,
@OrderType = 1 ,
@RecordCount = @RecordCount OUTPUT,
@totalPage = @totalPage OUTPUT
SELECT @RecordCount as N ' @RecordCount ' ,
@totalPage as N ' @totalPage '
SELECT ' Return Value ' = @return_value
GO
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
DECLARE
@return_value
int
,
@RecordCount int ,
@totalPage int
EXEC @return_value = [ dbo ] . [ p_generalTablePage ]
@strTbName = N ' (select * from webTable) s ' , -- 子表
@strFeilds = N ' wid,shortName,url,resume ' ,
@strOrder = N ' City desc,shortname desc ' ,
@strWhere = N ' siteid=34 ' ,
@PageSize = 20 ,
@PageIndex = 1 ,
@masterFeilds = N ' wid ' ,
@OrderType = 1 ,
@RecordCount = @RecordCount OUTPUT,
@totalPage = @totalPage OUTPUT
SELECT @RecordCount as N ' @RecordCount ' ,
@totalPage as N ' @totalPage '
SELECT ' Return Value ' = @return_value
GO
@RecordCount int ,
@totalPage int
EXEC @return_value = [ dbo ] . [ p_generalTablePage ]
@strTbName = N ' (select * from webTable) s ' , -- 子表
@strFeilds = N ' wid,shortName,url,resume ' ,
@strOrder = N ' City desc,shortname desc ' ,
@strWhere = N ' siteid=34 ' ,
@PageSize = 20 ,
@PageIndex = 1 ,
@masterFeilds = N ' wid ' ,
@OrderType = 1 ,
@RecordCount = @RecordCount OUTPUT,
@totalPage = @totalPage OUTPUT
SELECT @RecordCount as N ' @RecordCount ' ,
@totalPage as N ' @totalPage '
SELECT ' Return Value ' = @return_value
GO
够郁闷的了,这个编辑我老是打开弹出脚本有误。