Debug task - cannot find error in T-SQL

--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