新版本的SqlServer分布存储过程

最新推荐文章于 2023-06-07 15:32:03 发布

烈火蜓蜻

最新推荐文章于 2023-06-07 15:32:03 发布

阅读量906

点赞数

分类专栏：数据库文章标签： sql server 分页

本文链接：https://blog.csdn.net/Linux7985/article/details/52199700

版权

数据库专栏收录该内容

26 篇文章 0 订阅

订阅专栏

SQL 2005及以上版本

/****** Object:  StoredProcedure [dbo].[PageQuery]    Script Date: 04/13/2012 14:26:57 ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
-- =============================================  
-- Author:      <Author,,Name>  
-- Create date: <Create Date,,>  
-- Description: <Description,,>  
-- =============================================  
CREATE PROCEDURE [dbo].[Page_Query]  
    @TableName   NVARCHAR(255),         -- 表名  
    @Fields NVARCHAR(1000) = '*',       -- 需要返回的列  
    @OrderFields NVARCHAR(255)='',      -- 排序的字段名，不带ORDER BY 关键字，必填  
    @PageSize   INT = 10,               -- 页尺寸  
    @PageIndex  INT = 1,                -- 页码  
    @Where  NVARCHAR(1500) = '',        -- 查询条件 (注意: 不要加 where)  
    @NeedCount BIT = 0,  
    @RecordCount INT OUTPUT  
AS  
BEGIN  
    DECLARE @ExceSql NVARCHAR(MAX)  

    IF @Where !=''  
      SET @Where =' WHERE ' + @Where  

    SET @ExceSql='SELECT * FROM (SELECT ROW_NUMBER() OVER ( ORDER BY ' + @OrderFields + ') AS pos,' + @Fields + ' FROM ' + @TableName + @Where + ') AS sp WHERE pos BETWEEN '+STR( ( @PageIndex - 1 ) * @PageSize + 1 ) + ' AND ' + STR( @PageIndex * @PageSize )  
    EXEC (@ExceSql)  

    SET @RecordCount = 0  
    IF @NeedCount = 1  
    BEGIN  
        SET @ExceSql='SELECT @RecordCount=COUNT(1) FROM ' + @TableName + @Where  
        EXEC sp_executesql @ExceSql, N'@RecordCount INT OUTPUT', @RecordCount OUTPUT  
    END  
END

SQL 2012及以上版本

/****** Object:  StoredProcedure [dbo].[PageQuery]    Script Date: 04/13/2012 14:26:57 ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
-- =============================================  
-- Author:      <Author,,Name>  
-- Create date: <Create Date,,>  
-- Description: <Description,,>  
-- =============================================  
CREATE PROCEDURE [dbo].[Page_Query]  
    @TableName   NVARCHAR(255),         -- 表名  
    @Fields NVARCHAR(1000) = '*',       -- 需要返回的列  
    @OrderFields NVARCHAR(255)='',      -- 排序的字段名，不带ORDER BY 关键字，必填  
    @PageSize   INT = 10,               -- 页尺寸  
    @PageIndex  INT = 1,                -- 页码  
    @Where  NVARCHAR(1500) = '',        -- 查询条件 (注意: 不要加 where)  
    @NeedCount BIT = 0,  
    @RecordCount INT OUTPUT  
AS  
BEGIN  
    DECLARE @ExceSql NVARCHAR(MAX)  

    IF @Where !=''  
      SET @Where =' WHERE ' + @Where  

    SET @ExceSql='SELECT ' + @Fields + ' FROM ' + @TableName + @Where + ' ORDER BY ' + @OrderFields + ' OFFSET ' + STR( ( @PageIndex - 1 ) * @PageSize ) + ' ROWS FETCH NEXT ' + STR( @PageSize ) + ' ROWS ONLY'
    EXEC (@ExceSql)  

    SET @RecordCount = 0  
    IF @NeedCount = 1  
    BEGIN  
        SET @ExceSql='SELECT @RecordCount=COUNT(1) FROM ' + @TableName + @Where  
        EXEC sp_executesql @ExceSql, N'@RecordCount INT OUTPUT', @RecordCount OUTPUT  
    END  
END