• SQL 万能分页存储过程


    USE [TestDB]
    IF OBJECT_ID('Proc_Paging', 'P') IS NOT NULL    --删除同名存储过程
        DROP PROC Proc_Paging
    GO
    CREATE PROC Proc_Paging
        @TableFields NVARCHAR(512),
        @TableName NVARCHAR(512),
        @SqlWhere NVARCHAR(512),
        @OrderBy NVARCHAR(64),
        @PageIndex INT,
        @PageSize INT,
        @TotalCount INT OUTPUT
    AS
        DECLARE @SQL1 NVARCHAR(2048), @SQL2 NVARCHAR(2048)    --@SQL1和@SQL2最好设置为比较长的字符串,否则会因为SQL语句过长而导致执行失败
        --SET @SQL1 = N'SELECT TOP ' + CONVERT(VARCHAR(2), @PageSize) + ' * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ' + @OrderBy + ') AS NID, ' + @TableFields + ' FROM ' + @TableName + ' WHERE ' + @SqlWhere + ') AS TmpTable WHERE TmpTable.NID > (@PageIndex - 1) * @PageSize SELECT @TotalCount = COUNT(1) FROM ' + @TableName + ' WHERE ' + @SqlWhere  --用TOP关键字进行筛选
        SET @SQL1 = N'SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ' + @OrderBy + ') AS NID, ' + @TableFields + ' FROM ' + @TableName + ' WHERE ' + @SqlWhere + ') as TmpTable WHERE TmpTable.NID BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex* @PageSize SELECT @TotalCount = COUNT(1) FROM ' + @TableName + ' WHERE ' + @SqlWhere
        SET @SQL2 = N'@TableFields NVARCHAR(512),@TableName NVARCHAR(512),@SqlWhere NVARCHAR(512),@OrderBy NVARCHAR(64),@PageIndex INT,@PageSize INT,@TotalCount INT OUTPUT'
    EXEC SP_EXECUTESQL @SQL1, @SQL2, @TableFields, @TableName, @SqlWhere,@OrderBy,@PageIndex,@PageSize,@TotalCount OUTPUT
    PRINT @SQL1    --打印执行语句  
    
    GO    --测试用例
    DECLARE @Count INT = 0
    EXEC Proc_Paging '*', 'TestTable', '1=1', 'Id', 5, 10, @Count OUTPUT    --筛选发布时间小于当前时间的记录
  • 相关阅读:
    梯度下降_机器学习-李宏毅
    LeTex算法伪代码环境
    数据结构之线性表
    Java中的初始化块、构造器、静态初始化块的执行顺序
    Java中的内省(introspector)
    JSP (Java Server Page)
    eclipse的web工程默认部署到了哪里
    Persistence机制(永久保存/序列化Serialize)
    VC++中使用正则表达式RegExp
    Java中解析和生成xml
  • 原文地址:https://www.cnblogs.com/daimaxuejia/p/15870043.html
Copyright © 2020-2023  润新知