• 146 阅读
  • 1 回复

自己收集比较强大的分页存储过程 推荐

视频在线上传+队列转换FLV+水印+捉图+修复+获时+转3GP(API语言不限,开视频站必备!)
朋友的比较好的存储过程。优点是:性能非常的高,每次查询都是根据ID查询,每次都是对一半的数据进行分页。缺点是:当有多个排序条件时,分页数据显示会出现问题。(该问题在第二个网友的分页存储过程中有解决的方法)。

代码 复制 - 运行

USE [house]
GO

/****** Object:  StoredProcedure [dbo].[sp_viewPage]    Script Date: 10/11/2017 13:16:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:   David.Yan
-- Create date: 2007.12.17
-- Description: 
/*
高效通用分页存储过程(双向检索)
敬告:适用于单一主键或存在唯一值列的表或视图
ps:Sql语句为8000字节,调用时请注意传入参数及sql总长度不要超过指定范围
*/
-- =============================================
CREATE PROCEDURE [dbo].[sp_viewPage]
-- Add the parameters for the stored procedure here
@TableName VARCHAR(200),     --表名
@FieldList VARCHAR(2000),    --显示列名,如果是全部字段则为*
@PrimaryKey VARCHAR(100),    --单一主键或唯一值键
@Where VARCHAR(2000),        --查询条件 不含'where'字符,如id>10 and len(userid)>9
@Order VARCHAR(1000),        --排序 不含'order by'字符,如id asc,userid desc,必须指定asc或desc
--注意当@SortType=3时生效,记住一定要在最后加上主键,否则会让你比较郁闷
@SortType INT,               --排序规则 1:正序asc 2:倒序desc 3:多列排序方法
@RecorderCount INT,          --记录总数 0:会返回总记录
@PageSize INT,               --每页输出的记录数
@PageIndex INT,              --当前页数
@TotalCount INT OUTPUT,      --记返回总记录
@TotalPageCount INT OUTPUT   --返回总页数
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
IF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0
SET @Order = RTRIM(LTRIM(@Order))
SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey))
SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),' ','')
WHILE CHARINDEX(', ',@Order) > 0 OR CHARINDEX(' ,',@Order) > 0
BEGIN
SET @Order = REPLACE(@Order,', ',',')
SET @Order = REPLACE(@Order,' ,',',')
END
IF ISNULL(@TableName,'') = '' OR ISNULL(@FieldList,'') = ''
OR ISNULL(@PrimaryKey,'') = ''
OR @SortType < 1 OR @SortType >3
OR @RecorderCount < 0 OR @PageSize < 0 OR @PageIndex < 0
BEGIN
   PRINT('ERR_00参数错误')
   RETURN
END
IF @SortType = 3
BEGIN
IF (UPPER(RIGHT(@Order,4))!=' ASC' AND UPPER(RIGHT(@Order,5))!=' DESC')
BEGIN
   PRINT('ERR_02排序错误') RETURN END
END
DECLARE @new_where1 VARCHAR(1000)
DECLARE @new_where2 VARCHAR(1000)
DECLARE @new_order1 VARCHAR(1000)
DECLARE @new_order2 VARCHAR(1000)
DECLARE @new_order3 VARCHAR(1000)
DECLARE @Sql VARCHAR(8000)
DECLARE @SqlCount NVARCHAR(4000)
IF ISNULL(@where,'') = ''
BEGIN
SET @new_where1 = ' '
SET @new_where2 = ' WHERE '
END
ELSE
BEGIN
SET @new_where1 = ' WHERE ' + @where
SET @new_where2 = ' WHERE ' + @where + ' AND '
END
IF ISNULL(@order,'') = '' OR @SortType = 1 OR @SortType = 2
BEGIN
IF @SortType = 1
   BEGIN
   SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' ASC'
   SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' DESC'
   END
IF @SortType = 2
   BEGIN
   SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' DESC'
   SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' ASC'
   END
END
ELSE
BEGIN
SET @new_order1 = ' ORDER BY ' + @Order
END
IF @SortType = 3 AND CHARINDEX(','+@PrimaryKey+' ',','+@Order)>0
BEGIN
SET @new_order1 = ' ORDER BY ' + @Order
SET @new_order2 = @Order + ','
SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},')
SET @new_order2 = REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,')
SET @new_order2 = ' ORDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1)
IF @FieldList <> '*'
   BEGIN
   SET @new_order3 = REPLACE(REPLACE(@Order + ',','ASC,',','),'DESC,',',')
   SET @FieldList = ',' + @FieldList
   WHILE CHARINDEX(',',@new_order3)>0
    BEGIN
    IF CHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@FieldList+',')>0
     BEGIN
     SET @FieldList =
     @FieldList + ',' + SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3))
     END
    SET @new_order3 = SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3))
    END
   SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList))
   END
END
SET @SqlCount = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'
+ CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName + @new_where1
IF @RecorderCount = 0
BEGIN
EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',
@TotalCount OUTPUT,@TotalPageCount OUTPUT
END
ELSE
BEGIN
SELECT @TotalCount = @RecorderCount
END
IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)
BEGIN
SET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)
END
IF @PageIndex = 1 OR @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)
BEGIN
IF @PageIndex = 1 --返回第一页数据
   BEGIN
   SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
   + @TableName + @new_where1 + @new_order1
   END
IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) --返回最后一页数据
   BEGIN
   SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
   + 'SELECT TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize))
   + ' ' + @FieldList + ' FROM '
   + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '
   + @new_order1
   END
END
ELSE
BEGIN
IF @SortType = 1 --仅主键正序排序
   BEGIN
   IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
    BEGIN
    SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
    + @TableName + @new_where2 + @PrimaryKey + ' > '
    + '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP '
    + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey
    + ' FROM ' + @TableName
    + @new_where1 + @new_order1 +' ) AS TMP) '+ @new_order1
    END
   ELSE --反向检索
    BEGIN
    SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
    + 'SELECT TOP ' + STR(@PageSize) + ' '
    + @FieldList + ' FROM '
    + @TableName + @new_where2 + @PrimaryKey + ' < '
    + '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP '
    + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey
    + ' FROM ' + @TableName
    + @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2
    + ' ) AS TMP ' + @new_order1
    END
   END
IF @SortType = 2 --仅主键反序排序
   BEGIN
   IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
    BEGIN
    SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
    + @TableName + @new_where2 + @PrimaryKey + ' < '
    + '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP '
    + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey
    +' FROM '+ @TableName
    + @new_where1 + @new_order1 + ') AS TMP) '+ @new_order1
    END
   ELSE --反向检索
    BEGIN
    SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
    + 'SELECT TOP ' + STR(@PageSize) + ' '
    + @FieldList + ' FROM '
    + @TableName + @new_where2 + @PrimaryKey + ' > '
    + '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP '
    + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey
    + ' FROM ' + @TableName
    + @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2
    + ' ) AS TMP ' + @new_order1
    END
   END
IF @SortType = 3 --多列排序,必须包含主键,且放置最后,否则不处理
   BEGIN
   IF CHARINDEX(',' + @PrimaryKey + ' ',',' + @Order) = 0
    BEGIN
    PRINT('ERR_02') RETURN
    END
   IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
    BEGIN
    SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
    + 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
    + ' SELECT TOP ' + STR(@PageSize*@PageIndex) + ' ' + @FieldList
    + ' FROM ' + @TableName + @new_where1 + @new_order1 + ' ) AS TMP '
    + @new_order2 + ' ) AS TMP ' + @new_order1
    END
   ELSE --反向检索
    BEGIN
    SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
    + 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
    + ' SELECT TOP ' + STR(@TotalCount-@PageSize *@PageIndex+@PageSize) + ' ' + @FieldList
    + ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '
    + @new_order1 + ' ) AS TMP ' + @new_order1
    END
   END
END
EXEC(@Sql)


GO




ASP调用:

代码 复制 - 运行

 '定义command 对象调用名称 
 aspcc.CommandText = "sp_viewPage"
 '设置command调用类型是存储过程 (adaspccStoredProc = 4)
 aspcc.CommandType = 4

 '定义一个字符型输入参数
 aspcc.Parameters.Append aspcc.CreateParameter("@TableName", adVarChar, adParaminput, 200, sql_table)
 aspcc.Parameters.Append aspcc.CreateParameter("@FieldList", adVarChar, adParaminput, 2000, sql_Field)
 aspcc.Parameters.Append aspcc.CreateParameter("@PrimaryKey", adVarChar, adParaminput, 100, sql_id)
 aspcc.Parameters.Append aspcc.CreateParameter("@Where", adVarChar, adParaminput, 2000,sql_where)
 aspcc.Parameters.Append aspcc.CreateParameter("@Order", adVarChar, adParaminput, 1000, sql_order)
 aspcc.Parameters.Append aspcc.CreateParameter("@SortType", adInteger, adParaminput,4,2)
 aspcc.Parameters.Append aspcc.CreateParameter("@RecorderCount", adInteger, adParaminput,4,0)
 aspcc.Parameters.Append aspcc.CreateParameter("@PageSize", adInteger, adParaminput, 4, PageSize)
 aspcc.Parameters.Append aspcc.CreateParameter("@PageIndex", adInteger, adParaminput, 4,page)
 aspcc.Parameters.Append aspcc.CreateParameter("@TotalCount", adInteger, adParamOutput, 4,0)
 aspcc.Parameters.Append aspcc.CreateParameter("@TotalPageCount", adInteger, adParamOutput, 4,0)


	recordcount=aspcc.Parameters("@TotalCount").Value'输出总条数
	pagecount=aspcc.Parameters("@TotalPageCount").Value'输出总YE数




在用的还有(小鱼有修改):

代码 复制 - 运行

USE [house]
GO


/****** Object:  StoredProcedure [dbo].[sys_Page_v2]    Script Date: 10/11/2017 13:31:00 ******/
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO


--http://blog.csdn.net/qiaqia609/article/details/41445233
CREATE PROCEDURE [dbo].[sys_Page_v2]  
@PCount int output,    --总页数输出  
@RCount int output,    --总记录数输出  
@sys_Table nvarchar(100),    --查询表名  
@sys_Key varchar(50),        --主键  
@sys_Fields nvarchar(500),    --查询字段  
@sys_Where nvarchar(3000),    --查询条件  
@sys_Order nvarchar(500),    --排序字段  
@sys_Begin int,        --开始位置  
@sys_PageIndex int,        --当前页数  
@sys_PageSize int        --页大小  
AS  
SET NOCOUNT ON  
SET ANSI_WARNINGS ON  
IF @sys_PageSize < 0 OR @sys_PageIndex < 0  
BEGIN          
RETURN  
END
--执行毫秒开始
declare @d1 DateTime
set @d1=GETDATE()


DECLARE @new_where1 NVARCHAR(3000)  
DECLARE @new_order1 NVARCHAR(100)  
DECLARE @new_order2 NVARCHAR(100)  
DECLARE @Sql NVARCHAR(4000)  
DECLARE @SqlCount NVARCHAR(4000)  
DECLARE @Top int 


/*
if(@sys_Begin <=0)  
    set @sys_Begin=0  
else  
    set @sys_Begin=@sys_Begin-1  
*/  
if(@sys_PageIndex <=0)  
    set @sys_PageIndex=1 
set @sys_Begin=@sys_PageIndex-1
--以上为小鱼修正,@sys_Begin可以不用输入,其实现在已不接受参数




IF ISNULL(@sys_Where,'') = ''  
    SET @new_where1 = ' '  
ELSE  
    SET @new_where1 = ' WHERE ' + @sys_Where  
IF ISNULL(@sys_Order,'') <> ''   
BEGIN  
    SET @new_order1 = ' ORDER BY ' + Replace(@sys_Order,'desc','')  
    SET @new_order1 = Replace(@new_order1,'asc','desc')  
    SET @new_order2 = ' ORDER BY ' + @sys_Order  
END  
ELSE  
BEGIN  
    SET @new_order1 = ' ORDER BY ID DESC'  
    SET @new_order2 = ' ORDER BY ID ASC'  
END  


SET @SqlCount = 'SELECT @RCount=COUNT(1),@PCount=CEILING((COUNT(1)+0.0)/'  
            + CAST(@sys_PageSize AS NVARCHAR)+') FROM ' + @sys_Table + @new_where1  
EXEC SP_EXECUTESQL @SqlCount,N'@RCount INT OUTPUT,@PCount INT OUTPUT',  
               @RCount OUTPUT,@PCount OUTPUT  


IF @sys_PageIndex > CEILING((@RCount+0.0)/@sys_PageSize)    --如果输入的当前页数大于实际总页数,则把实际总页数赋值给当前页数  
BEGIN  
    SET @sys_PageIndex =  CEILING((@RCount+0.0)/@sys_PageSize)  
END  
/*
set @sql = 'select '+ @sys_fields +' from ' + @sys_Table + ' w1 '  
    + ' where '+ @sys_Key +' in ('  
        +'select top '+ ltrim(str(@sys_PageSize)) +' ' + @sys_Key + ' from '  
        +'('  
            +'select top ' + ltrim(STR(@sys_PageSize * @sys_PageIndex + @sys_Begin)) + ' ' + @sys_Key + ' FROM '  
        + @sys_Table + @new_where1 + @new_order2   
        +') w ' + @new_order1  
    +') ' + @new_order2  
print(@sql)  
Exec(@sql)  
*/
DECLARE @ThisTopList int = @sys_PageSize --当前页需要列多少条
DECLARE @ThisList int = @sys_PageSize * @sys_PageIndex --当前页计算来的条数
--print(@ThisList)  
--print(@RCount) 
IF @ThisList > @RCount
	SET @ThisTopList = @sys_PageSize - (@ThisList - @RCount)


set @sql = 'select '+ @sys_fields +' from ' + @sys_Table + ' w1 '  
    + ' where '+ @sys_Key +' in ('  
        +'select top '+ ltrim(str(@ThisTopList)) +' ' + @sys_Key + ' from '  
        +'('  
            +'select top ' + ltrim(STR(@ThisList + @sys_Begin)) + ' ' + @sys_Key + ' FROM '  
        + @sys_Table + @new_where1 + @new_order2   
        +') w ' + @new_order1  
    +') ' + @new_order2  
--print(@sql) 
Exec(@sql)


--执行毫秒计算输出
declare @d21 DateTime
set @d21=GETDATE()
print(datediff(ms,@d1,@d21)) 


GO

ASP调用:

代码 复制 - 运行

'定义command 对象调用名称 
 aspcc.CommandText = "sys_Page_v2"
 '设置command调用类型是存储过程 (adaspccStoredProc = 4)
 aspcc.CommandType = 4
 '定义一个字符型输入参数
 aspcc.Parameters.Append aspcc.CreateParameter("@PCount", adInteger, adParamOutput, 4,0)
 aspcc.Parameters.Append aspcc.CreateParameter("@RCount", adInteger, adParamOutput, 4,0)
 aspcc.Parameters.Append aspcc.CreateParameter("@sys_Table", adVarChar, adParaminput, 100, sql_table)
 aspcc.Parameters.Append aspcc.CreateParameter("@sys_Key", adVarChar, adParaminput, 50, sql_id)
 aspcc.Parameters.Append aspcc.CreateParameter("@sys_Fields", adVarChar, adParaminput, 500, sql_Field)
 aspcc.Parameters.Append aspcc.CreateParameter("@sys_Where", adVarChar, adParaminput, 3000,sql_where)
 aspcc.Parameters.Append aspcc.CreateParameter("@sys_Order", adVarChar, adParaminput, 500, sql_order)
 aspcc.Parameters.Append aspcc.CreateParameter("@sys_Begin", adInteger, adParaminput,4,0)
 aspcc.Parameters.Append aspcc.CreateParameter("@sys_PageIndex", adInteger, adParaminput, 4,page)
 aspcc.Parameters.Append aspcc.CreateParameter("@sys_PageSize", adInteger, adParaminput, 4, PageSize)


recordcount=aspcc.Parameters("@RCount").Value'输出总条数
pagecount=aspcc.Parameters("@PCount").Value'输出总YE数
本帖最后由 张小鱼 于 2017-10-11 13:34 编辑
小鱼的淘宝店铺-多多支持哇
视频在线上传+队列转换FLV+水印+捉图+修复+获时+转3GP(API语言不限,开视频站必备!)

代码 复制 - 运行

'调用存储过程
 adParamReturnValue = 4
 adParaminput = 1
 adParamOutput = 2
 adInteger = 3
 adVarChar = 200
 'response.Write(sql_where)
 set aspcc = Server.CreateObject("ADODB.Command")
 '建立连结
 aspcc.ActiveConnection = ConnStr
 '定义command 对象调用名称 
 aspcc.CommandText = "sp_viewPage"
 '设置command调用类型是存储过程 (adaspccStoredProc = 4)
 aspcc.CommandType = 4
小鱼的淘宝店铺-多多支持哇