Click here to Skip to main content
15,867,686 members
Articles / Database Development / SQL Server

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

Rate me:
Please Sign up or sign in to vote.
3.50/5 (3 votes)
29 May 2009CPOL 55.9K   157   14   1
WITH Clause AND EXEC sp_executesql Clause Used In MSSQL 2005
image.jpg

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

SQL
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

SQL
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

SQL
    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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
China China
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Generalgood article Pin
Donsw10-Jul-09 11:04
Donsw10-Jul-09 11:04 
Good but you might want to explain more than just show the code.

cheers,
Donsw
My Recent Article : Backup of Data files - Full and Incremental

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.