用于 MSSQL 2005 的 SQLProcedure， 包含带 RowNum 的自定义分页和不带 RowNum 的自定义分页， 适用于 MSSQL 2000 和 MSSQL 2005

• 下载源代码 - 950 B
• 下载演示代码 - 53.78 KB

引言

这是一个用于 MSSQL 2005 的自定义分页 SQL 存储过程。
我们经常使用依赖于我们编写的特定 SQL 存储过程的自定义分页控件。
简而言之，它非常有用且高效。

• 为什么使用 WITH 子句
  • 可重用，允许我们创建虚拟视图（用于存储 SQL）
  • 更易读，使重复的部分集中化并进行别名处理
  • 更高效，WITH 子句只计算一次
• 为什么使用 EXEC sp_executesql 子句
  • 动态，构建 SQL 将从不同的表中获取数据

这是 SQL 存储过程的代码。

最终版本，支持 MSSQL 2000

ALTER PROCEDURE dbo.kuuy_P_PagingFinal
 @getFields NVARCHAR(500)='', --字段名
    @tblName NVARCHAR(255)='', --表名
    @strWhere NVARCHAR(1500)='', --筛选语句
    @strOrder NVARCHAR(1000)='NewsID',--排序字段
    @keyName NVARCHAR(50)='NewsID', --键值
    @IsCount BIT=0, --是否计数
    @PageSize INT=10, --分页大小
    @CurrentPage INT=1, --当前页
    @ItemCount INT=0 OUTPUT
AS
 DECLARE @strCount NVARCHAR(1000) --Total Records
 DECLARE @strSQL NVARCHAR(4000) --SQL语句
    DEClARE @strTmp NVARCHAR(2000) --临时语句
 DECLARE @tmpTable VARCHAR(10) --临时表名
 Set @tmpTable = '#tmp_data'
 
 --条件判定
 IF (@strOrder <> '') --排序字段
        SET @strOrder = ' ORDER BY '+@strOrder
        
    IF(@strWhere <> '') --筛选字段
        SET @strWhere =' WHERE '+@strWhere
    
    IF (@IsCount <> 0)
        SET @strCount = ' SET @ItemCount=(SELECT COUNT(' + @keyName + ') _
					FROM '+@tblName+@strWhere+')'
    ELSE
        SET @strCount =''
    
 IF @CurrentPage > 1
 BEGIN
 --创建临时表
 SET @strSQL = 'SELECT IDENTITY(INT,1,1) as RowNum,' + @keyName + _
					'*1 as ID INTO '+ @tmpTable
 SET @strSQL = @strSQL + ' FROM ' + @tblName + ' T'+ @strWhere + @strOrder
 SET @strSQL = @strSQL + ' CREATE UNIQUE CLUSTERED INDEX Idx_UC_RowNum ON ' + _
						@tmpTable + '(RowNum)'
 
 SET @strTmp='(SELECT TOP '+ ltrim(str(@PageSize)) + ' ID  FROM '+@tmpTable + 
     ' WHERE RowNum > '+ ltrim(str((@CurrentPage-1)*@PageSize)) + 
     ' AND RowNum < '+ ltrim(str(@CurrentPage*@PageSize+1)) +') '
 
 SET @strSQL = @strSQL + ' SELECT ' + @getFields + ' FROM ' + _
	@tblName + ' WHERE ' + @keyName + ' IN ' + @strTmp + @strOrder
 
 --删除临时表
 SET @strSQL = @strSQL + ' '+' DROP TABLE '+@tmpTable
 END
 ELSE
  SET @strSQL = 'SELECT TOP '+ltrim(str(@PageSize))+' 0 AS RowNum,' + _
						@getFields + ' FROM'
+@tblName+@strWhere+@strOrder+@strCount
 
 PRINT @strSQL
 EXEC sp_executesql @strSQL,N'@ItemCount INT OUTPUT',@ItemCount OUTPUT 
 RETURN

带有 RowNum 的 SQL Server 2005 自定义分页存储过程

ALTER PROCEDURE [dbo].[kuuy_P_CustomPaging]
    @getFields NVARCHAR(500)='', --Columns_Name
    @tblName NVARCHAR(255)='', --Table_Name
    @strWhere NVARCHAR(1500)='', --Conditions
    @strOrder NVARCHAR(1000)='',--Order
    @IsCount BIT=0, --Identity Whether Return The Total Results
    @PageSize INT=10, --RowsCount You Want Fetch Once
    @CurrentPage INT=1, --CurrentPage
    @ItemCount INT=0 OUTPUT
AS
    DECLARE @strSQL NVARCHAR(4000) --Dynamic SQL string
    DEClARE @strTmp NVARCHAR(2000) --TEMP SQL String
    DECLARE @ItemCountValue INT
    DECLARE @strCount NVARCHAR(4000) --Total Records
    
    IF (@strOrder <> '')
        SET @strOrder = ' ORDER BY '+@strOrder
    ELSE
        SET @strOrder = ' ORDER BY ID'
    IF(@strWhere <> '')
        SET @strWhere =' WHERE '+@strWhere
    
       SET @strTmp='WITH T_Orders AS('+
                                     'SELECT ROW_NUMBER() OVER(' + @strOrder + ') _
					AS RowNum,' + @getFields + ' ' +
                                     'FROM ' + @tblName + ' ' + @strWhere + 
                                     ')'
    IF (@IsCount!=0)
        SET @strCount = ' SET @ItemCount=(SELECT COUNT(*) FROM '+@tblName+@strWhere+');'
    ELSE
        SET @strCount =''
    
    SET @strSQL = @strCount +
                  @strTmp + 'SELECT TOP ('+str(@PageSize)+') RowNum,' + _
				@getFields + ' ' +
                            'FROM T_Orders ' +
                            'WHERE RowNum BETWEEN ' + str((@CurrentPage-1)*_
			@PageSize+1) + ' AND '+ str(@CurrentPage*@PageSize)
                          
    EXEC sp_executesql @strSQL,N'@ItemCount INT OUTPUT',_
			@ItemCount=@ItemCountValue OUTPUT
    SET @ItemCount = @ItemCountValue
    RETURN

适用于 MSSQL 2000 和 MSSQL 2005 的不带 RowNum 的自定义分页存储过程

    ALTER PROCEDURE [dbo].[kuuy_P_CustomPaging_WithoutRowNum]
    @getFields NVARCHAR(500)='', --Columns_Name
    @tblName NVARCHAR(255)='', --Table_Name
    @strWhere NVARCHAR(1500)='', --Conditons
    @strOrder NVARCHAR(1000)='',--Order
    @keyName NVARCHAR(50)='ID', --KeyName
    @oprateKeyValue VARCHAR(2)='>', --Operate
    @funKeyValue NVARCHAR(100)='MAX(ID)', --Identity The Order Key value
    @IsCount BIT=0, --Identity Whether Return The Total Results
    @PageSize INT=10, --RowsCount You Want Fetch Once
    @CurrentPage INT=1, --CurrentPage
    @ItemCount INT=0 OUTPUT
AS
    DECLARE @strSQL NVARCHAR(4000) --Dynamic SQL string
    DEClARE @strTmp NVARCHAR(2000) --TEMP SQL String
    DECLARE @ItemCountValue INT
    DECLARE @strCount NVARCHAR(4000) --Total Records
    
    IF (@strOrder <> '')
        SET @strOrder = ' ORDER BY '+@strOrder
    ELSE
        SET @strOrder = ' ORDER BY ID'
    IF(@strWhere <> '')
    BEGIN
        IF(@CurrentPage > 1)
            SET @strWhere =' AND '+@strWhere
        ELSE
            SET @strWhere =' WHERE '+@strWhere
    END
       
    IF (@IsCount <> 0)
        SET @strCount = ' SET @ItemCount=(SELECT COUNT(*) FROM '+@tblName+@strWhere+')'
    ELSE
        SET @strCount =''
        
    IF(@CurrentPage > 1)
    BEGIN
           SET @strTmp='WITH T_Orders AS(SELECT TOP('+str(@CurrentPage*@PageSize)+')'_
		+@keyName+' FROM '+@tblName+@strWhere+@strOrder+')'
           SET @strSQL = @strTmp+'SELECT TOP ('+str(@PageSize)+')'+@getFields+' '+
                          'FROM '+@tblName + ' '+
                          'WHERE '+ @keyName + @oprateKeyValue +
                          '(SELECT ' + @funKeyValue + ' FROM (SELECT TOP(' + _
			str((@CurrentPage-1)*@PageSize) + ')* FROM T_Orders) AS T)'+ 
                        @strWhere+@strOrder
    END
       ELSE
           SET @strSQL = 'SELECT TOP('+str(@PageSize)+')'+@getFields+' _
				FROM '+@tblName+@strWhere+@strOrder
                          
    EXEC sp_executesql @strSQL,N'@ItemCount INT OUTPUT',_
				@ItemCount=@ItemCountValue OUTPUT
    PRINT @strSQL
    SET @ItemCount = @ItemCountValue
    RETURN