代码之家  ›  专栏  ›  技术社区  ›  Vikas

需要有关分页的常规sql存储过程的帮助吗

  •  0
  • Vikas  · 技术社区  · 14 年前

    我在SQL server 2005数据库中创建了以下用于常规分页的存储过程:

    USE [training]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[usp_Pagination1]  
        -- Add the parameters for the stored procedure here 
        @SqlColumns VARCHAR(MAX), 
        @SqlFriendlyColumns VARCHAR(MAX), 
        @SqlTableClause VARCHAR(MAX), 
        @StartRow INT, 
        @EndRow INT, 
        @SqlWhere VARCHAR(MAX), 
        @SqlOuterWhere VARCHAR(MAX), 
        @SqlRowNumOrderBy VARCHAR(MAX), 
        @SqlOuterOrderBy VARCHAR(MAX) 
    AS 
    DECLARE @rsSQL NVARCHAR(MAX) 
    DECLARE @rcSQL NVARCHAR(MAX) 
    BEGIN 
        -- SET NOCOUNT ON added to prevent extra result sets from 
        -- interfering with SELECT statements. 
        SET NOCOUNT ON; 
    /* 
    sample dynamically created SQL: 
        WITH PersonContact AS 
        ( 
            SELECT PC.FirstName, PC.LastName, PC.EmailAddress, 
            ROW_NUMBER() OVER(ORDER BY PC.ContactID) AS RowNumber 
            FROM Person.Contact PC 
        ) 
        SELECT FirstName, LastName, EmailAddress 
        FROM PersonContact 
        WHERE RowNumber > @StartRow AND RowNumber < @EndRow 
        ORDER BY LastName DESC, EmailAddress 
    */ 
        -- build pagination SQL, using StartRow and EndRow to determine 
        -- which results to output 
        SET @rsSQL = N' WITH tempTable AS ( ' +  
            N' SELECT ' +  
                @SqlColumns +  
            N' , ROW_NUMBER() OVER(ORDER BY ' +  
                @SqlRowNumOrderBy +  
            N' ) AS RowNumber ' + 
            N' FROM ' +  
                @SqlTableClause 
        IF @SqlWhere + '' <> '' 
            BEGIN 
                SET @rsSQL = @rsSQL +                   
                    N' WHERE ' +  
                        @SqlWhere 
            END 
        SET @rsSQL = @rsSQL + 
            N' ) SELECT ' + 
                @SqlFriendlyColumns + 
            N' FROM tempTable ' + 
            N' WHERE RowNumber >= ' +  
                CAST(@StartRow AS NVARCHAR(32)) +  
            N' AND RowNumber <= ' +  
                CAST(@EndRow AS NVARCHAR(32)) + 
            N' ORDER BY ' + 
                @SqlOuterOrderBy 
    
        -- uncomment PRINT to debug 
        PRINT @rsSQL 
        EXEC sp_executesql @rsSQL 
        -- build second recordset simple for the count 
        SET @rcSQL =  
                N'SELECT COUNT(*) AS CountAll FROM ' +  
                    @SqlTableClause 
        IF @SqlOuterWhere + '' <> '' 
            BEGIN 
                SET @rcSQL = @rcSQL +                   
                    N' WHERE ' +  
                          @SqlOuterWhere
            END 
    
        EXEC sp_executesql  @rcSQL     
        SET NOCOUNT OFF; 
    END
    

    USE [training]
    GO
    
    EXEC    [dbo].[usp_Pagination1]
            @SqlColumns = N'tab1.categoryId,tab1.categoryName,tab1.description,tab1.parentCategory,tab2.categoryName AS parentCategoryName',
            @SqlFriendlyColumns = N'categoryId,categoryName,description,parentCategory,parentCategoryName',
            @SqlTableClause = N'vpCategory tab1 LEFT JOIN vpCategory tab2 ON tab1.parentCategory = tab2.categoryId',
            @StartRow = 1,
            @EndRow = 1,
            @SqlWhere = N'tab1.categoryId = 1',
            @SqlOuterWhere = N'categoryId = 1',
            @SqlRowNumOrderBy = N'tab1.categoryId',
            @SqlOuterOrderBy = N'categoryId'
    
    GO
    

    当我为我的表vpCategory执行它时,我得到一条消息: 消息209,16级,状态1,行1

    但在结果选项卡中,它给了我正确的输出。

    “消息”选项卡还提供以下查询,这些查询是由过程生成的:

    WITH tempTable AS (  SELECT tab1.categoryId,tab1.categoryName,tab1.description,tab1.parentCategory,tab2.categoryName AS parentCategoryName , ROW_NUMBER() OVER(ORDER BY tab1.categoryId ) AS RowNumber  FROM vpCategory tab1 LEFT JOIN vpCategory tab2 ON tab1.parentCategory = tab2.categoryId WHERE tab1.categoryId = 1 ) SELECT categoryId,categoryName,description,parentCategory,parentCategoryName FROM tempTable  WHERE RowNumber >= 1 AND RowNumber <= 1 ORDER BY categoryId
    

    有人能帮我找出存储过程出了什么问题吗?

    2 回复  |  直到 14 年前
        1
  •  0
  •   Will A    14 年前

        2
  •  0
  •   Vikas    14 年前

    知道了!

    实际上,我们不需要外部代码。

     ALTER PROCEDURE [dbo].[usp_Pagination]  
        -- Add the parameters for the stored procedure here 
        @SqlColumns VARCHAR(MAX), 
        @SqlFriendlyColumns VARCHAR(MAX), 
        @SqlTableClause VARCHAR(MAX), 
        @StartRow INT, 
        @EndRow INT, 
        @SqlWhere VARCHAR(MAX), 
        @SqlRowNumOrderBy VARCHAR(MAX), 
        @SqlOuterOrderBy VARCHAR(MAX) 
    AS 
    DECLARE @rsSQL NVARCHAR(MAX) 
    DECLARE @rcSQL NVARCHAR(MAX) 
    BEGIN 
        -- SET NOCOUNT ON added to prevent extra result sets from 
        -- interfering with SELECT statements. 
        SET NOCOUNT ON; 
    /* 
    sample dynamically created SQL: 
        WITH PersonContact AS 
        ( 
            SELECT PC.FirstName, PC.LastName, PC.EmailAddress, 
            ROW_NUMBER() OVER(ORDER BY PC.ContactID) AS RowNumber 
            FROM Person.Contact PC 
        ) 
        SELECT FirstName, LastName, EmailAddress 
        FROM PersonContact 
        WHERE RowNumber > @StartRow AND RowNumber < @EndRow 
        ORDER BY LastName DESC, EmailAddress 
    */ 
        -- build pagination SQL, using StartRow and EndRow to determine 
        -- which results to output 
        SET @rsSQL = N' WITH tempTable AS ( ' +  
            N' SELECT ' +  
                @SqlColumns +  
            N' , ROW_NUMBER() OVER(ORDER BY ' +  
                @SqlRowNumOrderBy +  
            N' ) AS RowNumber ' + 
            N' FROM ' +  
                @SqlTableClause 
        IF @SqlWhere + '' <> '' 
            BEGIN 
                SET @rsSQL = @rsSQL +                   
                    N' WHERE ' +  
                        @SqlWhere 
            END 
        SET @rsSQL = @rsSQL + 
            N' ) SELECT ' + 
                @SqlFriendlyColumns + 
            N' FROM tempTable ' + 
            N' WHERE RowNumber >= ' +  
                CAST(@StartRow AS NVARCHAR(32)) +  
            N' AND RowNumber <= ' +  
                CAST(@EndRow AS NVARCHAR(32)) + 
            N' ORDER BY ' + 
                @SqlOuterOrderBy 
    
        -- uncomment PRINT to debug 
        PRINT @rsSQL 
        EXEC sp_executesql @rsSQL 
        -- build second recordset simple for the count 
        SET @rcSQL =  
                N'SELECT COUNT(*) AS CountAll FROM ' +  
                    @SqlTableClause 
        IF @SqlWhere + '' <> '' 
            BEGIN 
                SET @rcSQL = @rcSQL +                   
                    N' WHERE ' +  
                        @SqlWhere 
            END 
    
        EXEC sp_executesql  @rcSQL     
        SET NOCOUNT OFF; 
    END