欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

通用分页存储过程,支持子查询或联表查询 分页存储过程子查询联表查询 

程序员文章站 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

 

 

转自 http://www.cnblogs.com/rockdean/articles/rockdean.html