A SQLProcedure For MSSQL 2005 Of CustomPaging With RowNum And CustomPaging Without RowNum Both For MSSQL 2000 And MSSQL 2005

• Download source code- 950 B
• Download demo code - 53.78 KB

Introduction

This is a SQLProcedure For MSSQL 2005 Of CustomPaging.
We often use CustomPaging controls which are dependant on a special SQLProcedure we wrote.
In short, it's very useful and effective.

• Why use WITH clause
  • Reusable, enables us to create virtual views (which are used for storing the SQL)
  • More readable, make the repeated sections centralized and aliased
  • More effective, a WITH clause is calculated only once
• Why use EXEC sp_executesql clause
  • Dynamic, building the SQL will fetch data from different tables

Here is the code for the SQLProcedure.

Final Edition Support For 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

CustomPaging SQLProcedure For SQL2005 With RowNum

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

CustomPaging SQLProcedure Without RowNum Both For MSSQL 2000 And MSSQL 2005

    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