通用分页存储过程，支持子查询或联表查询 分页存储过程子查询联表查询
程序员文章站
2022-07-11 20:15:58
...
代码
Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->/****** 对象: 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
配合分页存储过程使用的标值量函数，用以计算总页数
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->
/****** 对象: 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。
eg:简单的实例
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->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
稍稍复杂点
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->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
联表查询
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->
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
内嵌子查询
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->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