?
Solved

Debug task - cannot find error in T-SQL

Posted on 2006-04-27
8
Medium Priority
?
178 Views
Last Modified: 2012-05-05



--region [dbo].[SelectPagedNewSearchByForeignKey]

------------------------------------------------------------------------------------------------------------------------
-- Procedure Name: [dbo].[SelectPagedNewSearchByForeignKey]
-- Date Generated: Friday, 28 April 2006
-- Author:         Stephen McCormack - stephenm@mwebsolutions.com.au
-- Company:        MWeb Solutions Pty Ltd
-- Software:            CodeSmith v3.1.6.594
-- Template:       StoredProcedures.cst
-- Comments:       'Server Side Paging with SQL Server' - http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=40505
--------- --------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[SelectPagedNewsSearchByForeignKey]

      -- paging parameters
      @pageNumber int = 1 ,
      @pageSize int = 10 ,
      @searchText varchar(50) ,

      -- foreign key(s)
      @newsCategory int
      
AS

      -- dont return number of records affected
      SET NOCOUNT ON
                  
      --isolation level
      SET TRANSACTION ISOLATION LEVEL READ COMMITTED

      -- parameter validation
      IF (@pageNumber < 1)      SET @pageNumber = 1
      IF (@pageSize < 1)            SET @pageSize = 10

      -- system function stores
      DECLARE
            @err_status int,       -- stores error status
            @row_count int            -- stores number of rows affected
            
      -- BEGIN TRANSACTION
      BEGIN TRANSACTION

      -- close page requests
      IF ( @pageNumber >= 1 AND @pageNumber < 20 )
      BEGIN

            ----------------------------------------------------
            -- ROWCOUNT method for first page (where page=1)
            ----------------------------------------------------
            IF ( @pageNumber = 1 )
            BEGIN
                  
                  -- set number of records to return
                  SET ROWCOUNT @pageSize      
                  
                  IF( @newsCategory IS NULL )
                  BEGIN

                        --0
                        IF @@TRANCOUNT <> 0
                        ROLLBACK TRANSACTION
                        RAISERROR( 'Must provide at least one optional parameter for ''[dbo].[SelectPagedNewSearchByForeignKey]''', 15, 1 )
                        RETURN -201 -- expecting parameter code!

                  END
                  ELSE
                  BEGIN

                        --1
                        SELECT
                                    [dbo].[News].[id], /* pk */
                                    [dbo].[News].[createdDate],
                                    [dbo].[News].[publishDate],
                                    [dbo].[News].[newsCategory], /* fk */
                                    [dbo].[NewsCategories].[name] AS [NewsCategoryName], /* fk name column */
                                    [dbo].[News].[title],
                                    [dbo].[News].[shortDescription]
                                    --[dbo].[News].[htmlDescription]
                        FROM
                              [dbo].[News]
                              INNER JOIN [dbo].[NewsCategories] ON [dbo].[News].[newsCategory] = [dbo].[NewsCategories].[id]
                        WHERE
                              [dbo].[News].[newsCategory] = @newsCategory
                        AND
                        (
                              -- search filters!
                              [dbo].[News].[title] LIKE '%' + @searchText + '%' OR
                              [dbo].[News].[shortDescription] LIKE '%' + @searchText + '%' OR
                              [dbo].[News].[htmlDescription] LIKE '%' + @searchText + '%'
                        )
                        ORDER BY
                              [dbo].[News].[createdDate] DESC
                                          

                        /* must use single statement immediately to store system functions
                        as all DML statements, SELECT, IF, PRINT and SET will reset @@error to zero */
                        SELECT
                              @err_status = @@ERROR ,
                              @row_count = @@ROWCOUNT


                  END
                  
                  -- reset row count
                  SET ROWCOUNT 0
                        
                  -- print sql
                  --PRINT( 'ROWCOUNT method used for PageNumber=' + CAST( @pageNumber AS VARCHAR) + ' and PageSize=' + CAST( @pageSize AS VARCHAR) + '.' )

            END            
            
            END            
            -------------------------------------------------------------------
            -- SUBQUERY method for close pages (where page > 1 and page < 20)
            --------------------------------------------------------------------    
            ELSE            
        BEGIN            
                                  
                  -- determine the number of records until first
                  DECLARE @NumRecsToFirst int
                  SET @NumRecsToFirst = @pageSize * (@pageNumber-1)
                  
                  -- set row count accordingly
                  SET ROWCOUNT @NumRecsToFirst      
                  
                  -- declare temp table variables
                  DECLARE @tmpNotIn TABLE
                  (
                        [id] [int] NOT NULL
                        PRIMARY KEY (id)
                  )
                  DECLARE @tmpIn TABLE
                  (
                        [id] [int] NOT NULL
                        PRIMARY KEY (id)
                  )
                  
                  IF( @newsCategory IS NULL )
                  BEGIN

                        --0
                        IF @@TRANCOUNT <> 0
                        ROLLBACK TRANSACTION
                        RAISERROR( 'Must provide at least one optional parameter for ''[dbo].[SelectPagedNewSearchByForeignKey]''', 15, 1 )
                        RETURN -201 -- expecting parameter code!

                  END
                  ELSE
                  BEGIN

                        --1
                        INSERT INTO @tmpNotIn
                        SELECT
                              [dbo].[News].[id] /* pk */
                        FROM
                              [dbo].[News]
                        WHERE
                              [dbo].[News].[newsCategory] = @newsCategory
                        AND
                        (
                              -- search filters!
                              [dbo].[News].[title] LIKE '%' + @searchText + '%' OR
                              [dbo].[News].[shortDescription] LIKE '%' + @searchText + '%' OR
                              [dbo].[News].[htmlDescription] LIKE '%' + @searchText + '%'
                        )
                        ORDER BY
                              [dbo].[News].[id] /* pk */

                        /* must use single statement immediately to store system functions
                        as all DML statements, SELECT, IF, PRINT and SET will reset @@error to zero */
                        SELECT
                              @err_status = @@ERROR ,
                              @row_count = @@ROWCOUNT

                        -- check for errors
                        IF @err_status <> 0
                        BEGIN
                              IF @@TRANCOUNT <> 0
                              ROLLBACK TRANSACTION
                              RAISERROR( 'Error occurred in stored procedure ''[dbo].[SelectPagedNewSearchByForeignKey]''', 10, 1 )
                              RETURN @err_status
                        END

                        -- ensure correct number of rows affected
                        IF @row_count > @NumRecsToFirst
                        BEGIN
                              IF @@TRANCOUNT <> 0
                              ROLLBACK TRANSACTION
                              RAISERROR( 'Unexpected number of rows affected by stored procedure ''[dbo].[SelectPagedNewSearchByForeignKey]''', 10, 1 )
                              RETURN -999 -- standardised return code for this - 'Unexpected rowcount'      
                        END

                        ----------------------------------------------------------------------------
                        -- now select all records that are NOT in the NOT IN set
                        ----------------------------------------------------------------------------
                        SET ROWCOUNT @pageSize

                        INSERT INTO @tmpIn
                        SELECT
                              [dbo].[News].[id] /* pk */
                        FROM
                              [dbo].[News]
                        WHERE
                              [dbo].[News].[id] /* pk */
                        NOT IN
                        (
                              SELECT
                                    *
                              FROM
                                    @tmpNotIn
                              --already ordered and cannot add ordering to view
                        )
                        AND
                        (
                              [dbo].[News].[newsCategory] = @newsCategory
                        )
                        AND
                        (
                              -- search filters!
                              [dbo].[News].[title] LIKE '%' + @searchText + '%' OR
                              [dbo].[News].[shortDescription] LIKE '%' + @searchText + '%' OR
                              [dbo].[News].[htmlDescription] LIKE '%' + @searchText + '%'
                        )
                        ORDER BY
                              [dbo].[News].[id] /* pk */

                        /* must use single statement immediately to store system functions
                        as all DML statements, SELECT, IF, PRINT and SET will reset @@error to zero */
                        SELECT
                              @err_status = @@ERROR ,
                              @row_count = @@ROWCOUNT

                        -- check for errors
                        IF @err_status <> 0
                        BEGIN
                              IF @@TRANCOUNT <> 0
                              ROLLBACK TRANSACTION
                              RAISERROR( 'Error occurred in stored procedure ''[dbo].[SelectPagedNewSearchByForeignKey]''', 10, 1 )
                              RETURN @err_status
                        END

                        -- ensure correct number of rows affected
                        IF @row_count > @pageSize
                        BEGIN
                              IF @@TRANCOUNT <> 0
                              ROLLBACK TRANSACTION
                              RAISERROR( 'Unexpected number of rows affected by stored procedure ''[dbo].[SelectPagedNewSearchByForeignKey]''', 10, 1 )
                              RETURN -999 -- standardised return code for this - 'Unexpected rowcount'      
                        END

                        -- reset rowcount
                        SET ROWCOUNT 0

                        ----------------------------------------------------------------------------
                        -- return the set of records
                        ----------------------------------------------------------------------------
                        SELECT
                              [dbo].[News].[id], /* pk */
                              [dbo].[News].[createdDate],
                              [dbo].[News].[publishDate],
                              [dbo].[News].[newsCategory], /* fk */
                              [dbo].[NewsCategories].[name] AS [NewsCategoryName], /* fk name column */
                              [dbo].[News].[title],
                              [dbo].[News].[shortDescription]
                              --[dbo].[News].[htmlDescription]
                        FROM
                              [dbo].[News]
                              INNER JOIN [dbo].[NewsCategories] ON [dbo].[News].[newsCategory] = [dbo].[NewsCategories].[id]
                        WHERE
                              [dbo].[News].[id] /* pk */
                        IN
                        (
                              SELECT
                                    *
                              FROM
                                    @tmpIn
                              --already ordered and cannot add ordering to view
                        )
                        ORDER BY
                              [dbo].[News].[createdDate] DESC
                        
                        /* must use single statement immediately to store system functions
                        as all DML statements, SELECT, IF, PRINT and SET will reset @@error to zero */
                        SELECT
                              @err_status = @@ERROR ,
                              @row_count = @@ROWCOUNT

                  END

                  -- print sql
                  --PRINT( 'SUBQUERY METHOD EXECUTED for PageNumber=' + CAST( @pageNumber AS VARCHAR) + ' and PageSize=' + CAST( @pageSize AS VARCHAR) + '.' )
            
            
            END

      END

      ----------------------------------------------------
      -- CURSOR method for distant pages
      ----------------------------------------------------  
    ELSE
      BEGIN
            
            -- determine paging variables
            DECLARE @intStartRow int
            SET @intStartRow = ( (@pageNumber - 1) * @pageSize + 1)            
            
            DECLARE @PrimaryKey VARCHAR(18)
            DECLARE @PrimaryKeyTable TABLE
            (
                  PK1  varchar(18) NOT NULL PRIMARY KEY
            )      
      
            IF( @newsCategory IS NULL )
            BEGIN

                  --0
                  IF @@TRANCOUNT <> 0
                  ROLLBACK TRANSACTION
                  RAISERROR( 'Must provide at least one optional parameter for ''[dbo].[SelectPagedNewSearchByForeignKey]''', 15, 1 )
                  RETURN -201 -- expecting parameter code!

            END
            ELSE
            BEGIN

                  --1
                  -- cursor used for all paging
                  DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
                  SELECT
                        [dbo].[News].[id] /* pk */
                  FROM
                        [dbo].[News]
                  WHERE
                        [dbo].[News].[newsCategory] = @newsCategory
                  AND
                  (
                        -- search filters!
                        [dbo].[News].[title] LIKE '%' + @searchText + '%' OR
                        [dbo].[News].[shortDescription] LIKE '%' + @searchText + '%' OR
                        [dbo].[News].[htmlDescription] LIKE '%' + @searchText + '%'
                  )
                  ORDER BY
                        [dbo].[News].[id] /* pk */

                  /* must use single statement immediately to store system functions
                  as all DML statements, SELECT, IF, PRINT and SET will reset @@error to zero */
                  SELECT
                        @err_status = @@ERROR ,
                        @row_count = @@ROWCOUNT

                  -- check for errors
                  IF @err_status <> 0
                  BEGIN
                        IF @@TRANCOUNT <> 0
                        ROLLBACK TRANSACTION
                        RAISERROR( 'Error occurred in stored procedure ''[dbo].[SelectPagedNewSearchByForeignKey]''', 10, 1 )
                        RETURN @err_status
                  END

                  -- ensure correct number of rows affected
                  IF @row_count > @pageSize
                  BEGIN
                        IF @@TRANCOUNT <> 0
                        ROLLBACK TRANSACTION
                        RAISERROR( 'Unexpected number of rows affected by stored procedure ''[dbo].[SelectPagedNewSearchByForeignKey]''', 10, 1 )
                        RETURN -999 -- standardised return code for this - 'Unexpected rowcount'      
                  END

                  OPEN PagingCursor
                  FETCH RELATIVE @intStartRow FROM PagingCursor INTO @PrimaryKey

                  WHILE @pageSize > 0 AND @@FETCH_STATUS = 0
                  BEGIN
                        INSERT @PrimaryKeyTable (PK1)  VALUES (@PrimaryKey)
                        FETCH NEXT FROM PagingCursor INTO @PrimaryKey
                        SET @pageSize = @pageSize - 1
                  END

                  -- close and dereference cursors
                  CLOSE             PagingCursor
                  DEALLOCATE        PagingCursor

                  ----------------------------------------------------------------------------
                  -- return the paged set
                  ----------------------------------------------------------------------------
                  SELECT
                        [dbo].[News].[id], /* pk */
                        [dbo].[News].[createdDate],
                        [dbo].[News].[publishDate],
                        [dbo].[News].[newsCategory], /* fk */
                        [dbo].[NewsCategories].[name] AS [NewsCategoryName], /* fk name column */
                        [dbo].[News].[title],
                        [dbo].[News].[shortDescription]
                        --[dbo].[News].[htmlDescription]
                  FROM
                        [dbo].[News]
                        INNER JOIN [dbo].[NewsCategories] ON [dbo].[News].[newsCategory] = [dbo].[NewsCategories].[id]
                  INNER JOIN @PrimaryKeyTable AS pkTable ON
                        [dbo].[News].[id] /* pk */
                          = pkTable.PK1
                  ORDER BY
                        [dbo].[News].[createdDate] DESC
                  
                  /* must use single statement immediately to store system functions
                  as all DML statements, SELECT, IF, PRINT and SET will reset @@error to zero */
                  SELECT
                        @err_status = @@ERROR ,
                        @row_count = @@ROWCOUNT

            END
      
            -- print sql
            --PRINT( 'CURSOR METHOD EXECUTED for PageNumber=' + CAST( @pageNumber AS VARCHAR) + ' and PageSize=' + CAST( @pageSize AS VARCHAR) + '.' )
      
      END

      -- check for errors
      IF @err_status <> 0
      BEGIN
            IF @@TRANCOUNT <> 0
            ROLLBACK TRANSACTION
            RAISERROR( 'Error occurred in stored procedure ''[dbo].[SelectPagedNewSearchByForeignKey]''', 10, 1 )
            RETURN @err_status
      END

      -- ensure correct number of rows affected
      IF @row_count > @pageSize
      BEGIN
            IF @@TRANCOUNT <> 0
            ROLLBACK TRANSACTION
            RAISERROR( 'Unexpected number of rows affected by stored procedure ''[dbo].[SelectPagedNewSearchByForeignKey]''', 10, 1 )
            RETURN -999 -- standardised return code for this - 'Unexpected rowcount'      
      END
      
      ----------------------------------------------------
      -- Return total number of records
      ----------------------------------------------------  
      SELECT
            COUNT(*) AS [RecordCount]
      FROM
            [dbo].[News]
            
      /* must use single statement immediately to store system functions
      as all DML statements, SELECT, IF, PRINT and SET will reset @@error to zero */
      SELECT
            @err_status = @@ERROR

      -- check for errors
      IF @err_status <> 0
      BEGIN
            IF @@TRANCOUNT <> 0
            ROLLBACK TRANSACTION
            RAISERROR( 'Error occurred in stored procedure ''[dbo].[SelectPagedNewSearchByForeignKey]''  when returning aggregate record count.', 10, 1 )
            RETURN @err_status
      END

      
      -- COMMIT TRANSACTION
      COMMIT TRANSACTION            

      -- turn back on the number of rows affected
      SET NOCOUNT OFF

      -- return success
      RETURN 0

      
--endregion

GO








0
Comment
Question by:smacca
  • 3
  • 3
  • 2
8 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16559418
Are you getting any error messages ?
0
 

Author Comment

by:smacca
ID: 16559617
Put in SQL Analyser and check it out. There error does tell much except the line it occurs on.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16559709
    ----------------------------------------
     -- CURSOR method for distant pages
     ELSE--------------------------------------Check whether How this else is possible, You hae already another else at the top
     BEGIN
         
          -- determine paging variables
          DECLARE @intStartRow int
          SET @intStartRow = ( (@pageNumber - 1) * @pageSize + 1)          
         
          DECLARE @PrimaryKey VARCHAR(18)
          DECLARE @PrimaryKeyTable TABLE
          (
               PK1  varchar(18) NOT NULL PRIMARY KEY
          )    
     
          IF( @newsCategory IS NULL )
          BEGIN

               --0
               IF @@TRANCOUNT <> 0
               ROLLBACK TRANSACTION
               RAISERROR( 'Must provide at least one optional parameter for ''[dbo].[SelectPagedNewSearchByForeignKe<wbr/>y]''', 15, 1 )
               RETURN -201 -- expecting parameter code!

          END
          ELSE
          BEGIN

               --1
               -- cursor used for all paging
               DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
               SELECT
                    [dbo].[News].[id] /* pk */
               FROM
                    [dbo].[News]
               WHERE
                    [dbo].[News].[newsCategory] = @newsCategory
               AND
               (
                    -- search filters!
                    [dbo].[News].[title] LIKE '%' + @searchText + '%' OR
                    [dbo].[News].[shortDescription] LIKE '%' + @searchText + '%' OR
                    [dbo].[News].[htmlDescription] LIKE '%' + @searchText + '%'
               )
               ORDER BY
                    [dbo].[News].[id] /* pk */

               /* must use single statement immediately to store system functions
               as all DML statements, SELECT, IF, PRINT and SET will reset @@error to zero */
               SELECT
                    @err_status = @@ERROR ,
                    @row_count = @@ROWCOUNT

               -- check for errors
               IF @err_status <> 0
               BEGIN
                    IF @@TRANCOUNT <> 0
                    ROLLBACK TRANSACTION
                    RAISERROR( 'Error occurred in stored procedure ''[dbo].[SelectPagedNewSearchByForeignKe<wbr/>y]''', 10, 1 )
                    RETURN @err_status
               END

               -- ensure correct number of rows affected
               IF @row_count > @pageSize
               BEGIN
                    IF @@TRANCOUNT <> 0
                    ROLLBACK TRANSACTION
                    RAISERROR( 'Unexpected number of rows affected by stored procedure ''[dbo].[SelectPagedNewSearchByForeignKe<wbr/>y]''', 10, 1 )
                    RETURN -999 -- standardised return code for this - 'Unexpected rowcount'    
               END

               OPEN PagingCursor
               FETCH RELATIVE @intStartRow FROM PagingCursor INTO @PrimaryKey

               WHILE @pageSize > 0 AND @@FETCH_STATUS = 0
               BEGIN
                    INSERT @PrimaryKeyTable (PK1)  VALUES (@PrimaryKey)
                    FETCH NEXT FROM PagingCursor INTO @PrimaryKey
                    SET @pageSize = @pageSize - 1
               END

               -- close and dereference cursors
               CLOSE             PagingCursor
               DEALLOCATE        PagingCursor

               ----------------------------------------<wbr/>----------<wbr/>----------<wbr/>----------<wbr/>------
               -- return the paged set
               ----------------------------------------<wbr/>----------<wbr/>----------<wbr/>----------<wbr/>------
               SELECT
                    [dbo].[News].[id], /* pk */
                    [dbo].[News].[createdDate],
                    [dbo].[News].[publishDate],
                    [dbo].[News].[newsCategory], /* fk */
                    [dbo].[NewsCategories].[name] AS [NewsCategoryName], /* fk name column */
                    [dbo].[News].[title],
                    [dbo].[News].[shortDescription]
                    --[dbo].[News].[htmlDescription]
               FROM
                    [dbo].[News]
                    INNER JOIN [dbo].[NewsCategories] ON [dbo].[News].[newsCategory] = [dbo].[NewsCategories].[id]
               INNER JOIN @PrimaryKeyTable AS pkTable ON
                    [dbo].[News].[id] /* pk */
                      = pkTable.PK1
               ORDER BY
                    [dbo].[News].[createdDate] DESC
               
               /* must use single statement immediately to store system functions
               as all DML statements, SELECT, IF, PRINT and SET will reset @@error to zero */
               SELECT
                    @err_status = @@ERROR ,
                    @row_count = @@ROWCOUNT

          END
     
          -- print sql
          --PRINT( 'CURSOR METHOD EXECUTED for PageNumber=' + CAST( @pageNumber AS VARCHAR) + ' and PageSize=' + CAST( @pageSize AS VARCHAR) + '.' )
     
     END

     -- check for errors
     IF @err_status <> 0
     BEGIN
          IF @@TRANCOUNT <> 0
          ROLLBACK TRANSACTION
          RAISERROR( 'Error occurred in stored procedure ''[dbo].[SelectPagedNewSearchByForeignKe<wbr/>y]''', 10, 1 )
          RETURN @err_status
     END

     -- ensure correct number of rows affected
     IF @row_count > @pageSize
     BEGIN
          IF @@TRANCOUNT <> 0
          ROLLBACK TRANSACTION
          RAISERROR( 'Unexpected number of rows affected by stored procedure ''[dbo].[SelectPagedNewSearchByForeignKe<wbr/>y]''', 10, 1 )
          RETURN -999 -- standardised return code for this - 'Unexpected rowcount'    
     END
     
     ----------------------------------------<wbr/>----------<wbr/>--
     -- Return total number of records
     ----------------------------------------<wbr/>----------<wbr/>--  
     SELECT
          COUNT(*) AS [RecordCount]
     FROM
          [dbo].[News]
         
     /* must use single statement immediately to store system functions
     as all DML statements, SELECT, IF, PRINT and SET will reset @@error to zero */
     SELECT
          @err_status = @@ERROR

     -- check for errors
     IF @err_status <> 0
     BEGIN
          IF @@TRANCOUNT <> 0
          ROLLBACK TRANSACTION
          RAISERROR( 'Error occurred in stored procedure ''[dbo].[SelectPagedNewSearchByForeignKe<wbr/>y]''  when returning aggregate record count.', 10, 1 )
          RETURN @err_status
     END

     
     -- COMMIT TRANSACTION
     COMMIT TRANSACTION          

     -- turn back on the number of rows affected
     SET NOCOUNT OFF

     -- return success
     RETURN 0

     
--endregion

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16559732
Better you take a print out of this and check the 'end's are proper...
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16560084
you have an problem with the end on line 284!

which basically means you've either not got enough END's or 1 too many

check out that your logic has matching pairs of BEGIN and ENDs

hth
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 total points
ID: 16560100
if you remove this second end here


          END          
         
 --         END          <---
          -------------------------------------------------------------------
          -- SUBQUERY method for close pages (where page > 1 and page < 20)
          --------------------------------------------------------------------    


then it compiles ok  (is this the duplicate one?)
0
 

Author Comment

by:smacca
ID: 16560851
Thanks everyone for your comments. I will have to go through the logic that creates them. It uses binary recursion so can get complex. I will provide results tomorrow. off to bed here in Oz. zzzzzzzzzzz
0
 

Author Comment

by:smacca
ID: 16636776
Solution:




--region [dbo].[SelectPagedNewsSearchByForeignKey]

------------------------------------------------------------------------------------------------------------------------
-- Procedure Name: [dbo].[SelectPagedNewsSearchByForeignKey]
-- Date Generated: Tuesday, 9 May 2006
-- Author:         Stephen McCormack - stephenm@mwebsolutions.com.au
-- Company:        MWeb Solutions Pty Ltd
-- Software:            CodeSmith v3.1.6.594
-- Template:       StoredProcedures.cst
-- Comments:       'Server Side Paging with SQL Server' - http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=40505
--------- --------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[SelectPagedNewsSearchByForeignKey]

      -- paging parameters
      @pageNumber int = 1 ,
      @pageSize int = 10 ,
      @searchText varchar(50) ,

      -- foreign key(s)
      @newsCategory int
      
AS

      -- dont return number of records affected
      SET NOCOUNT ON
                  
      --isolation level
      SET TRANSACTION ISOLATION LEVEL READ COMMITTED

      -- parameter validation
      IF (@pageNumber < 1)      SET @pageNumber = 1
      IF (@pageSize < 1)            SET @pageSize = 10

      -- system function stores
      DECLARE
            @err_status int,       -- stores error status
            @row_count int            -- stores number of rows affected
            
      -- BEGIN TRANSACTION
      BEGIN TRANSACTION

      -- close page requests
      IF ( @pageNumber >= 1 AND @pageNumber < 20 )
      BEGIN

            ----------------------------------------------------
            -- ROWCOUNT method for first page (where page=1)
            ----------------------------------------------------
            IF ( @pageNumber = 1 )
            BEGIN
                  
                  -- set number of records to return
                  SET ROWCOUNT @pageSize      
                  
                  IF( @newsCategory IS NULL )
                  BEGIN

                        --0
                        IF @@TRANCOUNT <> 0
                        ROLLBACK TRANSACTION
                        RAISERROR( 'Must provide at least one optional parameter for ''[dbo].[SelectPagedNewsSearchByForeignKey]''', 15, 1 )
                        RETURN -201 -- expecting parameter code!

                  END
                  ELSE
                  BEGIN

                        --1
                        SELECT
                                    [dbo].[News].[id], /* pk */
                                    [dbo].[News].[createdDate],
                                    [dbo].[News].[publishDate],
                                    [dbo].[News].[newsCategory], /* fk */
                                    [dbo].[NewsCategories].[name] AS [NewsCategoryName], /* fk name column */
                                    [dbo].[News].[title],
                                    [dbo].[News].[shortDescription]
                                    --[dbo].[News].[htmlDescription]
                        FROM
                              [dbo].[News]
                              INNER JOIN [dbo].[NewsCategories] ON [dbo].[News].[newsCategory] = [dbo].[NewsCategories].[id]
                        WHERE
                              [dbo].[News].[newsCategory] = @newsCategory
                        AND
                        (
                              -- search filters!
                              [dbo].[News].[title] LIKE '%' + @searchText + '%' OR
                              [dbo].[News].[shortDescription] LIKE '%' + @searchText + '%' OR
                              [dbo].[News].[htmlDescription] LIKE '%' + @searchText + '%'
                        )
                        ORDER BY
                              [dbo].[News].[createdDate] DESC
                        

                        /* must use single statement immediately to store system functions
                        as all DML statements, SELECT, IF, PRINT and SET will reset @@error to zero */
                        SELECT
                              @err_status = @@ERROR ,
                              @row_count = @@ROWCOUNT


                  END
                  
                  -- reset row count
                  SET ROWCOUNT 0
                        
                  -- print sql
                  --PRINT( 'ROWCOUNT method used for PageNumber=' + CAST( @pageNumber AS VARCHAR) + ' and PageSize=' + CAST( @pageSize AS VARCHAR) + '.' )

            END      
            
            -------------------------------------------------------------------
            -- SUBQUERY method for close pages (where page > 1 and page < 20)
            --------------------------------------------------------------------    
            ELSE            
        BEGIN            
                                  
                  -- determine the number of records until first
                  DECLARE @NumRecsToFirst int
                  SET @NumRecsToFirst = @pageSize * (@pageNumber-1)
                  
                  -- set row count accordingly
                  SET ROWCOUNT @NumRecsToFirst      
                  
                  -- declare temp table variables
                  DECLARE @tmpNotIn TABLE
                  (
                        [id] [int] NOT NULL
                        PRIMARY KEY (id)
                  )
                  DECLARE @tmpIn TABLE
                  (
                        [id] [int] NOT NULL
                        PRIMARY KEY (id)
                  )
                  
                  IF( @newsCategory IS NULL )
                  BEGIN

                        --0
                        IF @@TRANCOUNT <> 0
                        ROLLBACK TRANSACTION
                        RAISERROR( 'Must provide at least one optional parameter for ''[dbo].[SelectPagedNewsSearchByForeignKey]''', 15, 1 )
                        RETURN -201 -- expecting parameter code!

                  END
                  ELSE
                  BEGIN

                        --1
                        INSERT INTO @tmpNotIn
                        SELECT
                              [dbo].[News].[id] /* pk */
                        FROM
                              [dbo].[News]
                        WHERE
                              [dbo].[News].[newsCategory] = @newsCategory
                        ORDER BY
                              [dbo].[News].[id] /* pk */

                        /* must use single statement immediately to store system functions
                        as all DML statements, SELECT, IF, PRINT and SET will reset @@error to zero */
                        SELECT
                              @err_status = @@ERROR ,
                              @row_count = @@ROWCOUNT

                        -- check for errors
                        IF @err_status <> 0
                        BEGIN
                              IF @@TRANCOUNT <> 0
                              ROLLBACK TRANSACTION
                              RAISERROR( 'Error occurred in stored procedure ''[dbo].[SelectPagedNewsSearchByForeignKey]''', 10, 1 )
                              RETURN @err_status
                        END

                        -- ensure correct number of rows affected
                        IF @row_count > @NumRecsToFirst
                        BEGIN
                              IF @@TRANCOUNT <> 0
                              ROLLBACK TRANSACTION
                              RAISERROR( 'Unexpected number of rows affected by stored procedure ''[dbo].[SelectPagedNewsSearchByForeignKey]''', 10, 1 )
                              RETURN -999 -- standardised return code for this - 'Unexpected rowcount'      
                        END

                        ----------------------------------------------------------------------------
                        -- now select all records that are NOT in the NOT IN set
                        ----------------------------------------------------------------------------
                        SET ROWCOUNT @pageSize

                        INSERT INTO @tmpIn
                        SELECT
                              [dbo].[News].[id] /* pk */
                        FROM
                              [dbo].[News]
                        WHERE
                              [dbo].[News].[id] /* pk */
                        NOT IN
                        (
                              SELECT
                                    *
                              FROM
                                    @tmpNotIn
                              --already ordered and cannot add ordering to view
                        )
                        AND
                        (
                              [dbo].[News].[newsCategory] = @newsCategory
                        )
                        AND
                        (
                              -- search filters!
                              [dbo].[News].[title] LIKE '%' + @searchText + '%' OR
                              [dbo].[News].[shortDescription] LIKE '%' + @searchText + '%' OR
                              [dbo].[News].[htmlDescription] LIKE '%' + @searchText + '%'
                        )
                        ORDER BY
                              [dbo].[News].[id] /* pk */

                        /* must use single statement immediately to store system functions
                        as all DML statements, SELECT, IF, PRINT and SET will reset @@error to zero */
                        SELECT
                              @err_status = @@ERROR ,
                              @row_count = @@ROWCOUNT

                        -- check for errors
                        IF @err_status <> 0
                        BEGIN
                              IF @@TRANCOUNT <> 0
                              ROLLBACK TRANSACTION
                              RAISERROR( 'Error occurred in stored procedure ''[dbo].[SelectPagedNewsSearchByForeignKey]''', 10, 1 )
                              RETURN @err_status
                        END

                        -- ensure correct number of rows affected
                        IF @row_count > @pageSize
                        BEGIN
                              IF @@TRANCOUNT <> 0
                              ROLLBACK TRANSACTION
                              RAISERROR( 'Unexpected number of rows affected by stored procedure ''[dbo].[SelectPagedNewsSearchByForeignKey]''', 10, 1 )
                              RETURN -999 -- standardised return code for this - 'Unexpected rowcount'      
                        END

                        -- reset rowcount
                        SET ROWCOUNT 0

                        ----------------------------------------------------------------------------
                        -- return the set of records
                        ----------------------------------------------------------------------------
                        SELECT
                              [dbo].[News].[id], /* pk */
                              [dbo].[News].[createdDate],
                              [dbo].[News].[publishDate],
                              [dbo].[News].[newsCategory], /* fk */
                              [dbo].[NewsCategories].[name] AS [NewsCategoryName], /* fk name column */
                              [dbo].[News].[title],
                              [dbo].[News].[shortDescription]
                              --[dbo].[News].[htmlDescription]
                        FROM
                              [dbo].[News]
                              INNER JOIN [dbo].[NewsCategories] ON [dbo].[News].[newsCategory] = [dbo].[NewsCategories].[id]
                        WHERE
                              [dbo].[News].[id] /* pk */
                        IN
                        (
                              SELECT
                                    *
                              FROM
                                    @tmpIn

                              --already ordered and cannot add ordering to view
                        )
                        ORDER BY
                              [dbo].[News].[createdDate] DESC
                        
                        /* must use single statement immediately to store system functions
                        as all DML statements, SELECT, IF, PRINT and SET will reset @@error to zero */
                        SELECT
                              @err_status = @@ERROR ,
                              @row_count = @@ROWCOUNT

                  END

                  -- print sql
                  --PRINT( 'SUBQUERY METHOD EXECUTED for PageNumber=' + CAST( @pageNumber AS VARCHAR) + ' and PageSize=' + CAST( @pageSize AS VARCHAR) + '.' )
            
            
            END

      END
      ----------------------------------------------------
      -- CURSOR method for distant pages
      ----------------------------------------------------  
    ELSE
      BEGIN
            
            -- determine paging variables
            DECLARE @intStartRow int
            SET @intStartRow = ( (@pageNumber - 1) * @pageSize + 1)            
            
            DECLARE @PrimaryKey VARCHAR(18)
            DECLARE @PrimaryKeyTable TABLE
            (
                  PK1  varchar(18) NOT NULL PRIMARY KEY
            )      
      
            IF( @newsCategory IS NULL )
            BEGIN

                  --0
                  IF @@TRANCOUNT <> 0
                  ROLLBACK TRANSACTION
                  RAISERROR( 'Must provide at least one optional parameter for ''[dbo].[SelectPagedNewsSearchByForeignKey]''', 15, 1 )
                  RETURN -201 -- expecting parameter code!

            END
            ELSE
            BEGIN

                  --1
                  -- cursor used for all paging
                  DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
                  SELECT
                        [dbo].[News].[id] /* pk */
                  FROM
                        [dbo].[News]
                  WHERE
                        [dbo].[News].[newsCategory] = @newsCategory
                  AND
                  (
                        -- search filters!
                        [dbo].[News].[title] LIKE '%' + @searchText + '%' OR
                        [dbo].[News].[shortDescription] LIKE '%' + @searchText + '%' OR
                        [dbo].[News].[htmlDescription] LIKE '%' + @searchText + '%'
                  )
                  ORDER BY
                        [dbo].[News].[id] /* pk */

                  /* must use single statement immediately to store system functions
                  as all DML statements, SELECT, IF, PRINT and SET will reset @@error to zero */
                  SELECT
                        @err_status = @@ERROR ,
                        @row_count = @@ROWCOUNT

                  -- check for errors
                  IF @err_status <> 0
                  BEGIN
                        IF @@TRANCOUNT <> 0
                        ROLLBACK TRANSACTION
                        RAISERROR( 'Error occurred in stored procedure ''[dbo].[SelectPagedNewsSearchByForeignKey]''', 10, 1 )
                        RETURN @err_status
                  END

                  -- ensure correct number of rows affected
                  IF @row_count > @pageSize
                  BEGIN
                        IF @@TRANCOUNT <> 0
                        ROLLBACK TRANSACTION
                        RAISERROR( 'Unexpected number of rows affected by stored procedure ''[dbo].[SelectPagedNewsSearchByForeignKey]''', 10, 1 )
                        RETURN -999 -- standardised return code for this - 'Unexpected rowcount'      
                  END

                  OPEN PagingCursor
                  FETCH RELATIVE @intStartRow FROM PagingCursor INTO @PrimaryKey

                  WHILE @pageSize > 0 AND @@FETCH_STATUS = 0
                  BEGIN
                        INSERT @PrimaryKeyTable (PK1)  VALUES (@PrimaryKey)
                        FETCH NEXT FROM PagingCursor INTO @PrimaryKey
                        SET @pageSize = @pageSize - 1
                  END

                  -- close and dereference cursors
                  CLOSE             PagingCursor
                  DEALLOCATE        PagingCursor

                  ----------------------------------------------------------------------------
                  -- return the paged set
                  ----------------------------------------------------------------------------
                  SELECT
                        [dbo].[News].[id], /* pk */
                        [dbo].[News].[createdDate],
                        [dbo].[News].[publishDate],
                        [dbo].[News].[newsCategory], /* fk */
                        [dbo].[NewsCategories].[name] AS [NewsCategoryName], /* fk name column */
                        [dbo].[News].[title],
                        [dbo].[News].[shortDescription]
                        --[dbo].[News].[htmlDescription]
                  FROM
                        [dbo].[News]
                        INNER JOIN [dbo].[NewsCategories] ON [dbo].[News].[newsCategory] = [dbo].[NewsCategories].[id]
                  INNER JOIN @PrimaryKeyTable AS pkTable ON
                        [dbo].[News].[id] /* pk */
                          = pkTable.PK1
                  ORDER BY
                        [dbo].[News].[createdDate] DESC
                  
                  /* must use single statement immediately to store system functions
                  as all DML statements, SELECT, IF, PRINT and SET will reset @@error to zero */
                  SELECT
                        @err_status = @@ERROR ,
                        @row_count = @@ROWCOUNT

            END
      
            -- print sql
            --PRINT( 'CURSOR METHOD EXECUTED for PageNumber=' + CAST( @pageNumber AS VARCHAR) + ' and PageSize=' + CAST( @pageSize AS VARCHAR) + '.' )
      
      END

      -- check for errors
      IF @err_status <> 0
      BEGIN
            IF @@TRANCOUNT <> 0
            ROLLBACK TRANSACTION
            RAISERROR( 'Error occurred in stored procedure ''[dbo].[SelectPagedNewsSearchByForeignKey]''', 10, 1 )
            RETURN @err_status
      END

      -- ensure correct number of rows affected
      IF @row_count > @pageSize
      BEGIN
            IF @@TRANCOUNT <> 0
            ROLLBACK TRANSACTION
            RAISERROR( 'Unexpected number of rows affected by stored procedure ''[dbo].[SelectPagedNewsSearchByForeignKey]''', 10, 1 )
            RETURN -999 -- standardised return code for this - 'Unexpected rowcount'      
      END
      
      ----------------------------------------------------
      -- Return total number of records
      ----------------------------------------------------  
      SELECT
            COUNT(*) AS [RecordCount]
      FROM
            [dbo].[News]
            
      /* must use single statement immediately to store system functions
      as all DML statements, SELECT, IF, PRINT and SET will reset @@error to zero */
      SELECT
            @err_status = @@ERROR

      -- check for errors
      IF @err_status <> 0
      BEGIN
            IF @@TRANCOUNT <> 0
            ROLLBACK TRANSACTION
            RAISERROR( 'Error occurred in stored procedure ''[dbo].[SelectPagedNewsSearchByForeignKey]''  when returning aggregate record count.', 10, 1 )
            RETURN @err_status
      END

      
      -- COMMIT TRANSACTION
      COMMIT TRANSACTION            

      -- turn back on the number of rows affected
      SET NOCOUNT OFF

      -- return success
      RETURN 0

      
--endregion
GO
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question