网站建设对接视频,江苏建设集团有限公司网站,wordpress 返回列表,衡阳seo优化最近换了家新公司#xff0c;但是新公司没有使用分页的存储过程。那我就自个写一个往项目上套 #xff08;效率怎么样就不怎么清楚没有详细的测试过#xff09; CREATE PROCEDURE [dbo].[pro_common_pageList](tab NVARCHAR(MAX) ,---表名PrimaryKey VARCHAR(100) , --主键I…最近换了家新公司但是新公司没有使用分页的存储过程。那我就自个写一个往项目上套 效率怎么样就不怎么清楚没有详细的测试过 CREATE PROCEDURE [dbo].[pro_common_pageList](tab NVARCHAR(MAX) ,---表名PrimaryKey VARCHAR(100) , --主键IDstrFld NVARCHAR(MAX) , --字段字符串strWhere NVARCHAR(MAX) , --where条件 IsDistinct BIT ,PageIndex INT , --页码PageSize INT , --每页容纳的记录数Order VARCHAR(255) , --排序字段及规则,不用加order bySort VARCHAR(20) desc ,TotalCount INT OUTPUT --总记录数)
ASBEGINSET NOCOUNT ON;DECLARE strSql VARCHAR(MAX);IF strFld IS NULLOR strFld BEGIN SET strFld *;END; IF Order IS NULLOR Order BEGIN SET Order PrimaryKey;END;
--计算总记录数DECLARE sqlRecordCount NVARCHAR(MAX); --得到总记录条数的语句DECLARE recordCount INT; --保存总记录条数的变量DECLARE distinct NVARCHAR(20) ;IF IsDistinct 1BEGIN SET distinct DISTINCT ;END; SET sqlRecordCount NSELECT recordCountCOUNT(1) FROM (SELECT distinct strFld FROM tab WHERE 11 strWhere ) AS TEMPTABLE;EXEC sp_executesql sqlRecordCount, NrecordCount int output,recordCount OUTPUT;SET TotalCount recordCount;--数据查询
--当PageIndex1使用TOP效率较快IF PageIndex 1BEGIN SET strSql SELECT distinct TOP STR(PageSize) strFld ,0 AS rownum FROM tab WHERE 11 strWhere ORDER BY Order Sort;END; ELSEBEGIN SET strSql SELECT * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY PrimaryKey ) AS rownum FROM ;
--是否去重1去重IF IsDistinct 1BEGIN SET strSql strSql (SELECT DISTINCT strFld FROM tab WHERE 11 strWhere ) AS S ;END;ELSEBEGIN SET strSql strSql tab WHERE 11 strWhere;END; SET strSql strSql ) AS Dwhere WHERE Dwhere.rownum BETWEEN CAST(( ( PageIndex - 1 ) * PageSize ) 1 AS NVARCHAR(20));SET strSql strSql AND CAST(( PageIndex * PageSize ) AS NVARCHAR(20));SET strSql strSql ORDER BY Order Sort;END; PRINT(strSql)EXEC (strSql);SET NOCOUNT OFF;END; View Code 转载于:https://www.cnblogs.com/witeem/p/9067132.html
