sql server万能分页存储过程

最新推荐文章于 2023-10-08 14:37:22 发布

懒笔头

最新推荐文章于 2023-10-08 14:37:22 发布

阅读量118

点赞数

分类专栏： SQL

本文链接：https://blog.csdn.net/csdnsunyf/article/details/103566817

版权

SQL 专栏收录该内容

21 篇文章 0 订阅

订阅专栏

USE [xxx]
GO
/****** Object:  StoredProcedure [dbo].[p_Page]    scrip{过滤}t Date: 08/07/2016 11:56:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:                ITSVSE
-- Create date: 2015/11/04
-- Descrip{过滤}tion:        分页
-- =============================================
ALTER PROCEDURE [dbo].[p_Page]
        @PageIndex INT=1, 
        @PageSize INT=10,
        @TbName VARCHAR(50),
        @WhereStr NVARCHAR(1000)=NULL,
        @OrderBy VARCHAR(50)=' ORDER BY ID',
        @Total BIGINT OUTPUT
AS
BEGIN
        SET NOCOUNT ON;
        SELECT @Total=0
        DECLARE @strSQL nvarchar(4000) -- 主Sql          
        IF @WhereStr='' OR @WhereStr IS NULL
        BEGIN                
                SET @strSQL='SELECT @Total=COUNT(0) FROM ['+@TbName +'] WHERE 1=1 '
                EXEC sp_executesql @strSQL,N'@Total BIGINT OUTPUT',@Total OUTPUT;  
                SET @strSQL='SELECT * FROM ['+@TbName+'] WHERE ID IN (SELECT ID FROM (SELECT ID,ROW_NUMBER() OVER ('+@OrderBy+') AS num FROM ['+@TbName+']) AS settable WHERE num BETWEEN '+STR(((@PageIndex-1)*@PageSize)+1)+' AND '+STR(@PageIndex*@PageSize) +')'                
        END
        ELSE
        BEGIN 
                PRINT 'NOT NULL'
                SET @strSQL='SELECT @Total=COUNT(0) FROM ['+@TbName +'] WHERE 1=1 '+@WhereStr
                EXEC sp_executesql @strSQL,N'@Total BIGINT OUTPUT',@Total OUTPUT;  
                SET @strSQL='SELECT * FROM ['+@TbName+'] WHERE ID IN (SELECT ID FROM (SELECT ID,ROW_NUMBER() OVER ('+@OrderBy+') AS num FROM ['+@TbName+'] WHERE 1=1 '+@WhereStr+ ') AS settable WHERE num BETWEEN '+STR((@PageIndex-1)*@PageSize+1)+' AND '+STR(@PageIndex*@PageSize) +')'
        END 
        EXEC(@strSQL)        
END

sql万能分页存储过程