SQL Server 2005通用分页代码

本文介绍了如何在SQL Server 2005中实现通用的分页查询,提供了一个存储过程示例。为了高效使用,建议为表指定主键,并在调用存储过程时传入排序条件。若未指定排序,存储过程会自动查找并基于主键进行升序分页,但这种方式可能因搜索主键而导致性能下降。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

--通用的SQL Server 2005 分页程序,由该存储过程自动生成。

生成存储过程注意:

  • 该分页存储过程需要传递表名。
  • 尽量为该表建立主键。

 

 

CREATE PROC #AutoGeneration_Query_P
@TABLENAME VARCHAR(50)
AS
BEGIN
 
DECLARE @HOST_NAME VARCHAR(200)
 
DECLARE @GET_DATE DATETIME
 
DECLARE @SQLROC  VARCHAR(8000)
 
DECLARE @DESCRIPTION VARCHAR(4000)
 
SELECT @SQLROC='',@DESCRIPTION='',
   
@HOST_NAME=HOST_NAME(),@GET_DATE=GETDATE()
 
SET @SQLROC=@SQLROC+'IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID(''SP_'+@TABLENAME+'_Query'') AND XTYPE IN (N''P''))'+CHAR(10)
 
SET @SQLROC=@SQLROC+SPACE(5)+'DROP PROC SP_'+@TABLENAME+'_Query'+CHAR(10)
 
SET @SQLROC=@SQLROC+'GO '
 
SET @DESCRIPTION=@DESCRIPTION+'/*+--------------------------------------+'+CHAR(10)                        
 
SET @DESCRIPTION=@DESCRIPTION+'| 过程名称:SP_'+@TABLENAME+'_Query'+CHAR(10)                        
 
SET @DESCRIPTION=@DESCRIPTION+'| 功能说明:根据条件获取表'+@TABLENAME+'的记录的分页存储过程'+CHAR(10)                        
 
SET @DESCRIPTION=@DESCRIPTION+'| 入口参数:@SearchCondition:搜索条件,    @OrderList:分页排序方式'+CHAR(10)                    
 
SET @DESCRIPTION=@DESCRIPTION+'|            @PageSize:页大小,            @PageIndex:当前页'+CHAR(10
 
SET @DESCRIPTION=@DESCRIPTION+'| 过程返回:返回记录数'+CHAR(10)                        
 
SET @DESCRIPTION=@DESCRIPTION+'| 维护记录:Y/A'+CHAR(10)                        
 
SET @DESCRIPTION=@DESCRIPTION+'| 使用案例:SP_'+@TABLENAME+'_Query '''','''',20,10'+CHAR(10)                        
 
SET @DESCRIPTION=@DESCRIPTION+'| 工作站名:'+@HOST_NAME+''+CHAR(10)                        
 
SET @DESCRIPTION=@DESCRIPTION+'| 联系方式:Spark.Zou@hotmail.com'+CHAR(10)                        
 
SET @DESCRIPTION=@DESCRIPTION+'| 创建日期:'+CONVERT(VARCHAR(20),@GET_DATE,120)+''+CHAR(10)                         
 
SET @DESCRIPTION=@DESCRIPTION+'+--------------------------------------+*/'+CHAR(10)                        
 
SELECT @SQLROC=@SQLROC+CHAR(10)+@DESCRIPTION+'CREATE PROC SP_'+@TABLENAME+'_Query'                        
 
SET @SQLROC=@SQLROC+CHAR(13)+CHAR(10)+SPACE(4)+'@SearchCondition'+SPACE(20-LEN('@SearchCondition'))+'VARCHAR(2000), --查询条件'                        
 
SET @SQLROC=@SQLROC+CHAR(13)+CHAR(10)+SPACE(4)+'@OrderList'+SPACE(20-LEN('@OrderList'))+'VARCHAR(1000), --排序列表'                        
 
SET @SQLROC=@SQLROC+CHAR(13)+CHAR(10)+SPACE(4)+'@PageSize'+SPACE(20-LEN('@PageSize'))+'INT=10,           --每页的大小'                
 
SET @SQLROC=@SQLROC+CHAR(13)+CHAR(10)+SPACE(4)+'@PageIndex'+SPACE(20-LEN('@PageIndex'))+'INT               --要显示的页码'    
 
SET @SQLROC=@SQLROC+CHAR(10)+'AS'+CHAR(10)+'BEGIN'                
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'SET NOCOUNT ON'                
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'DECLARE @SQL VARCHAR(200)'                
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'DECLARE @KEYCOLUMNS VARCHAR(200)'                
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'DECLARE @DBNAME VARCHAR(20)'                
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'SELECT @SQL='''',@KEYCOLUMNS='''',@DBNAME=DB_NAME() '                
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'IF ISNULL(@SearchCondition,'''')=''''  AND ISNULL(@OrderList,'''')<>'''''                
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'BEGIN'                
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'SET @SQL=''SELECT * FROM (    '                
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'                SELECT ROW_NUMBER()OVER(ORDER BY ''+@OrderList+'' ) AS ROWID,* FROM '+@TABLENAME+' '
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'            ) AS MYTABLE '     
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'    WHERE ROWID BETWEEN ''+LTRIM(@PageSize)+''*(''+LTRIM(@PageIndex)+''-1)+1 and ''+LTRIM(@PageSize)+''*''+LTRIM(@PageIndex)+'' '' '             
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'END'                
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'IF ISNULL(@SearchCondition,'''')=''''  AND ISNULL(@OrderList,'''')='''''                
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'BEGIN'                
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' SELECT @KEYCOLUMNS=@KEYCOLUMNS+'',''+A.NAME'                                            
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' FROM SYSCOLUMNS A INNER JOIN  SYSTYPES B'                                            
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' ON A.XUSERTYPE=B.XUSERTYPE    AND ID=OBJECT_ID('''+@TABLENAME+''')'                                        
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' AND  EXISTS(SELECT 1 FROM SYSOBJECTS '                                            
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'                WHERE XTYPE=''PK'' AND PARENT_OBJ=A.ID AND NAME IN ('                            
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'                    SELECT NAME FROM SYSINDEXES '                        
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'                    WHERE INDID IN('                         
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'                    SELECT INDID FROM SYSINDEXKEYS '                         
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'                    WHERE ID = A.ID AND COLID=A.COLID'                         
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'                                )'             
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'                                            
 SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+
' )'                                             
 SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+
' SET @KEYCOLUMNS=STUFF(@KEYCOLUMNS,1,1,'''')'                                             
 SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+
' IF ISNULL(@KEYCOLUMNS,'''')=''''  '                                             
 SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+
' BEGIN'                                             
 SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+
'    raiserror(''SQL Server 2005 中需要提供排序字段,默认为表的主键字段'',16,1,@DBNAME)'                                         
 SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+
'    RETURN '                 
 SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+
' END'                     
 SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+
' SET @SQL=''SELECT * FROM (    '                    
 SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+
'                SELECT ROW_NUMBER()OVER(ORDER BY ''+@KEYCOLUMNS+'' ) AS ROWID,* FROM '+@TABLENAME+' '    
 SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+
'            ) AS MYTABLE '         
 SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+
'    WHERE ROWID BETWEEN ''+LTRIM(@PageSize)+''*(''+LTRIM(@PageIndex)+''-1)+1 and ''+LTRIM(@PageSize)+''*''+LTRIM(@PageIndex)+''  '' '                 
 SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+
'END'                    
 SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+
'IF ISNULL(@SearchCondition,'''')<>''''  AND ISNULL(@OrderList,'''')='''''                    
 SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+
'BEGIN'                    
 SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+
' SELECT @KEYCOLUMNS=@KEYCOLUMNS+'',''+A.NAME'                    
 SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+
' FROM SYSCOLUMNS A INNER JOIN  SYSTYPES B'                    
 SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+
' ON A.XUSERTYPE=B.XUSERTYPE    AND ID=OBJECT_ID('''+@TABLENAME+''')'                
 SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+
' AND  EXISTS(SELECT 1 FROM SYSOBJECTS '                    
 SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+
'                WHERE XTYPE=''PK'' AND PARENT_OBJ=A.ID AND NAME IN ('    
 SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+
'                    SELECT NAME FROM SYSINDEXES '
 SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+
'                    WHERE INDID IN(' 
 SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+
'                    SELECT INDID FROM SYSINDEXKEYS '                         
 SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+
'                    WHERE ID = A.ID AND COLID=A.COLID'                         
 SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+
'                                )'             
 SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+
'                                            
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' )'                                             
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' SET @KEYCOLUMNS=STUFF(@KEYCOLUMNS,1,1,'''')'                                             
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' IF ISNULL(@KEYCOLUMNS,'''')='''' '                                             
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' BEGIN'                                             
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'    raiserror(''SQL Server 2005 中需要提供排序字段,默认为表的主键字段'',16,1,@DBNAME)'                                         
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'    RETURN '                                         
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' END'                                             
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+' SET @SQL=''SELECT * FROM (    '                                            
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'                SELECT ROW_NUMBER()OVER(ORDER BY ''+@KEYCOLUMNS+'' ) AS ROWID,* FROM '+@TABLENAME+' '                            
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'            ) AS MYTABLE '                                 
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'    WHERE ROWID BETWEEN ''+LTRIM(@PageSize)+''*(''+LTRIM(@PageIndex)+''-1)+1 and ''+LTRIM(@PageSize)+''*''+LTRIM(@PageIndex)+'' '                                         
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'    AND @SearchCondition '' '                                         
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'END'                
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'IF ISNULL(@SearchCondition,'''')<>''''  AND ISNULL(@OrderList,'''')<>'''' '                
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'BEGIN'                
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'        SET @SQL=''SELECT * FROM (    '        
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'                SELECT ROW_NUMBER()OVER(ORDER BY ''+@OrderList+'' ) AS ROWID,* FROM '+@TABLENAME+' '
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'            ) AS MYTABLE '     
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'        WHERE ROWID BETWEEN ''+LTRIM(@PageSize)+''*(''+LTRIM(@PageIndex)+''-1)+1 and ''+LTRIM(@PageSize)+''*''+LTRIM(@PageIndex)+'' '         
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'        AND @SearchCondition '' '          
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'END'                
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'EXEC(@SQL)'                
 
SET @SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'SET NOCOUNT OFF'                 
 
SET @SQLROC=@SQLROC+CHAR(10)+'END'                
 
PRINT @SQLROC+CHAR(10)+'GO '                
END                
GO                
                
CREATE PROC #SP_Generation_Query
@TABLENAMES VARCHAR(8000)
AS 
BEGIN
DECLARE @I INT
DECLARE @TABLENAME VARCHAR(100)
SET @I=CHARINDEX(',',@TABLENAMES)
WHILE @I>0
BEGIN
 
SET @TABLENAME=LEFT(@TABLENAMES,@I-1)
 
EXEC #AutoGeneration_Query_P @TABLENAME
 
SET @TABLENAMES=RIGHT(@TABLENAMES,LEN(@TABLENAMES)-@I)
 
SET @I=CHARINDEX(',',@TABLENAMES)
END
IF LEN(@TABLENAMES)>0
BEGIN
 
EXEC #AutoGeneration_Query_P @TABLENAMES
END
END
GO

#SP_Generation_Query 
'tb_human_plan2'

drop proc #SP_Generation_Query
drop proc #AutoGeneration_Query_P 


 

--使用生成的存储过程注意:

  • 尽量为分页传递排序条件
  • 如果没有为分页传递排序条件、系统自动去搜索表的主键,默认以表的主键升序为分页条件。由于搜索表的主键消耗资源比较大,所以效率很降低。

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值