Why am I receiving an error regarding temporary table already exists when I have dropped it??

Why am I receiving an error regarding temporary table already exists when I have dropped it??
I have provided all the sql below to:

  * Create Table
  * Stored Procedure - with temporary table problem (I have provided entire procedure, simply need to check in Query Analyzer to spot error).

I can somewhat see why the error occurs, it is because I have multiple location where I implicitly insert records into a #tmpXXXX table.
HOWEVER, I have ensured the table is dropped before these statements are reached.

Are my drop statements for the temp table ok?
Is tempdb the location to look for the temp tables and is the ".." syntax separating them correct??

      -- if temporary tables exist, drop them now
      IF OBJECT_ID('tempdb..#tmpIn') IS NOT NULL
            DROP TABLE #tmpIn
      IF OBJECT_ID('tempdb..#tmpNot') IS NOT NULL
            DROP TABLE #tmpNot

If so, why can't I drop the table and created nested logic statements that re-create them - they are only ever recreated once before they are dropped again.

                You will have to go the "SUBQUERY" method part in the middle of the procedure. As mentioned, if you just check it Query Analyser it will identify the areas (i.e. "thanks captain obvious!" - LOL)


------------------------------------------


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Resorts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Resorts]
GO

CREATE TABLE [dbo].[Resorts] (
      [id] [int] IDENTITY (1, 1) NOT NULL ,
      [createdDate] [datetime] NOT NULL ,
      [country] [char] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
      [title] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
      [shortDescription] [varchar] (500) COLLATE Latin1_General_CI_AS NOT NULL ,
      [htmlDescription] [varchar] (4000) COLLATE Latin1_General_CI_AS NOT NULL ,
      [htmlMountainFacts] [varchar] (4000) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Resorts] WITH NOCHECK ADD
      CONSTRAINT [PK_Resorts] PRIMARY KEY  CLUSTERED
      (
            [id]
      )  ON [PRIMARY]
GO

-----------------------------------------------------------


CREATE PROCEDURE [dbo].[SelectPagedResortsBy]

      -- paging parameters
      @pageNumber int = 1 ,
      @pageSize int = 10 ,

      @country char(2) = NULL,
      @title varchar(50) = NULL

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( @country IS NULL )
                  BEGIN

                        IF( @title IS NULL )
                        BEGIN

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

                        END
                        ELSE
                        BEGIN

                              --01
                              SELECT
                                          [dbo].[Resorts].[id], /* pk */
                                          [dbo].[Resorts].[createdDate],
                                          [dbo].[Resorts].[country], /* fk */
                                          [dbo].[Countries].[name] AS [CountryName], /* fk name column */
                                          [dbo].[Resorts].[title],
                                          [dbo].[Resorts].[shortDescription],
                                          [dbo].[Resorts].[htmlDescription],
                                          [dbo].[Resorts].[htmlMountainFacts]
                              FROM
                                    [dbo].[Resorts]
                                    INNER JOIN [dbo].[Countries] ON [dbo].[Resorts].[country] = [dbo].[Countries].[iso_code]
                              WHERE
                                    [dbo].[Resorts].[title] = @title
                              ORDER BY
                                    [dbo].[Resorts].[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
                  END
                  ELSE
                  BEGIN

                        IF( @title IS NULL )
                        BEGIN

                              --10
                              SELECT
                                          [dbo].[Resorts].[id], /* pk */
                                          [dbo].[Resorts].[createdDate],
                                          [dbo].[Resorts].[country], /* fk */
                                          [dbo].[Countries].[name] AS [CountryName], /* fk name column */
                                          [dbo].[Resorts].[title],
                                          [dbo].[Resorts].[shortDescription],
                                          [dbo].[Resorts].[htmlDescription],
                                          [dbo].[Resorts].[htmlMountainFacts]
                              FROM
                                    [dbo].[Resorts]
                                    INNER JOIN [dbo].[Countries] ON [dbo].[Resorts].[country] = [dbo].[Countries].[iso_code]
                              WHERE
                                    [dbo].[Resorts].[country] = @country
                              ORDER BY
                                    [dbo].[Resorts].[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
                        ELSE
                        BEGIN

                              --11
                              SELECT
                                          [dbo].[Resorts].[id], /* pk */
                                          [dbo].[Resorts].[createdDate],
                                          [dbo].[Resorts].[country], /* fk */
                                          [dbo].[Countries].[name] AS [CountryName], /* fk name column */
                                          [dbo].[Resorts].[title],
                                          [dbo].[Resorts].[shortDescription],
                                          [dbo].[Resorts].[htmlDescription],
                                          [dbo].[Resorts].[htmlMountainFacts]
                              FROM
                                    [dbo].[Resorts]
                                    INNER JOIN [dbo].[Countries] ON [dbo].[Resorts].[country] = [dbo].[Countries].[iso_code]
                              WHERE
                                    [dbo].[Resorts].[country] = @country
                                    AND [dbo].[Resorts].[title] = @title
                              ORDER BY
                                    [dbo].[Resorts].[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
                  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
                  
                  -- if temporary tables exist, drop them now
                  IF OBJECT_ID('tempdb..#tmpIn') IS NOT NULL
                        DROP TABLE #tmpIn
                  IF OBJECT_ID('tempdb..#tmpNot') IS NOT NULL
                        DROP TABLE #tmpNot
            
                  IF( @country IS NULL )
                  BEGIN

                        IF( @title IS NULL )
                        BEGIN

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

                        END
                        ELSE
                        BEGIN

                              --01
                              SELECT
                                    [dbo].[Resorts].[id] /* pk */
                              INTO
                                    #tmpNot
                              FROM
                                    [dbo].[Resorts]
                              WHERE
                                    [dbo].[Resorts].[title] = @title
                              ORDER BY
                                    [dbo].[Resorts].[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].[SelectPagedResortsBy]''', 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].[SelectPagedResortsBy]''', 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
                              SELECT
                                    [dbo].[Resorts].[id] /* pk */
                              INTO
                                    #tmpIn
                              FROM
                                    [dbo].[Resorts]
                              WHERE
                                    [dbo].[Resorts].[id] /* pk */
                              NOT IN
                              (
                                    SELECT
                                          *
                                    FROM
                                          #tmpNot
                                    --already ordered and cannot add ordering to view
                              )
                              AND
                              (
                                    [dbo].[Resorts].[title] = @title
                              )
                              ORDER BY
                                    [dbo].[Resorts].[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].[SelectPagedResortsBy]''', 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].[SelectPagedResortsBy]''', 10, 1 )
                                    RETURN -999 -- standardised return code for this - 'Unexpected rowcount'      
                              END

                              -- reset rowcount
                              SET ROWCOUNT 0

                              ----------------------------------------------------------------------------
                              -- return the set of records
                              ----------------------------------------------------------------------------
                              SELECT
                                    [dbo].[Resorts].[id], /* pk */
                                    [dbo].[Resorts].[createdDate],
                                    [dbo].[Resorts].[country], /* fk */
                                    [dbo].[Countries].[name] AS [CountryName], /* fk name column */
                                    [dbo].[Resorts].[title],
                                    [dbo].[Resorts].[shortDescription],
                                    [dbo].[Resorts].[htmlDescription],
                                    [dbo].[Resorts].[htmlMountainFacts]
                              FROM
                                    [dbo].[Resorts]
                                    INNER JOIN [dbo].[Countries] ON [dbo].[Resorts].[country] = [dbo].[Countries].[iso_code]
                              WHERE
                                    [dbo].[Resorts].[id] /* pk */
                              IN
                              (
                                    SELECT
                                          *
                                    FROM
                                          #tmpIn
                                    --already ordered and cannot add ordering to view
                              )
                              ORDER BY
                                    [dbo].[Resorts].[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
                  END
                  ELSE
                  BEGIN

                        IF( @title IS NULL )
                        BEGIN

                              --10
                              SELECT
                                    [dbo].[Resorts].[id] /* pk */
                              INTO
                                    #tmpNot
                              FROM
                                    [dbo].[Resorts]
                              WHERE
                                    [dbo].[Resorts].[country] = @country
                              ORDER BY
                                    [dbo].[Resorts].[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].[SelectPagedResortsBy]''', 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].[SelectPagedResortsBy]''', 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
                              SELECT
                                    [dbo].[Resorts].[id] /* pk */
                              INTO
                                    #tmpIn
                              FROM
                                    [dbo].[Resorts]
                              WHERE
                                    [dbo].[Resorts].[id] /* pk */
                              NOT IN
                              (
                                    SELECT
                                          *
                                    FROM
                                          #tmpNot
                                    --already ordered and cannot add ordering to view
                              )
                              AND
                              (
                                    [dbo].[Resorts].[country] = @country
                              )
                              ORDER BY
                                    [dbo].[Resorts].[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].[SelectPagedResortsBy]''', 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].[SelectPagedResortsBy]''', 10, 1 )
                                    RETURN -999 -- standardised return code for this - 'Unexpected rowcount'      
                              END

                              -- reset rowcount
                              SET ROWCOUNT 0

                              ----------------------------------------------------------------------------
                              -- return the set of records
                              ----------------------------------------------------------------------------
                              SELECT
                                    [dbo].[Resorts].[id], /* pk */
                                    [dbo].[Resorts].[createdDate],
                                    [dbo].[Resorts].[country], /* fk */
                                    [dbo].[Countries].[name] AS [CountryName], /* fk name column */
                                    [dbo].[Resorts].[title],
                                    [dbo].[Resorts].[shortDescription],
                                    [dbo].[Resorts].[htmlDescription],
                                    [dbo].[Resorts].[htmlMountainFacts]
                              FROM
                                    [dbo].[Resorts]
                                    INNER JOIN [dbo].[Countries] ON [dbo].[Resorts].[country] = [dbo].[Countries].[iso_code]
                              WHERE
                                    [dbo].[Resorts].[id] /* pk */
                              IN
                              (
                                    SELECT
                                          *
                                    FROM
                                          #tmpIn
                                    --already ordered and cannot add ordering to view
                              )
                              ORDER BY
                                    [dbo].[Resorts].[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
                        ELSE
                        BEGIN

                              --11
                              SELECT
                                    [dbo].[Resorts].[id] /* pk */
                              INTO
                                    #tmpNot
                              FROM
                                    [dbo].[Resorts]
                              WHERE
                                    [dbo].[Resorts].[country] = @country
                                    AND [dbo].[Resorts].[title] = @title
                              ORDER BY
                                    [dbo].[Resorts].[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].[SelectPagedResortsBy]''', 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].[SelectPagedResortsBy]''', 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
                              SELECT
                                    [dbo].[Resorts].[id] /* pk */
                              INTO
                                    #tmpIn
                              FROM
                                    [dbo].[Resorts]
                              WHERE
                                    [dbo].[Resorts].[id] /* pk */
                              NOT IN
                              (
                                    SELECT
                                          *
                                    FROM
                                          #tmpNot
                                    --already ordered and cannot add ordering to view
                              )
                              AND
                              (
                                    [dbo].[Resorts].[country] = @country
                                    AND [dbo].[Resorts].[title] = @title
                              )
                              ORDER BY
                                    [dbo].[Resorts].[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].[SelectPagedResortsBy]''', 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].[SelectPagedResortsBy]''', 10, 1 )
                                    RETURN -999 -- standardised return code for this - 'Unexpected rowcount'      
                              END

                              -- reset rowcount
                              SET ROWCOUNT 0

                              ----------------------------------------------------------------------------
                              -- return the set of records
                              ----------------------------------------------------------------------------
                              SELECT
                                    [dbo].[Resorts].[id], /* pk */
                                    [dbo].[Resorts].[createdDate],
                                    [dbo].[Resorts].[country], /* fk */
                                    [dbo].[Countries].[name] AS [CountryName], /* fk name column */
                                    [dbo].[Resorts].[title],
                                    [dbo].[Resorts].[shortDescription],
                                    [dbo].[Resorts].[htmlDescription],
                                    [dbo].[Resorts].[htmlMountainFacts]
                              FROM
                                    [dbo].[Resorts]
                                    INNER JOIN [dbo].[Countries] ON [dbo].[Resorts].[country] = [dbo].[Countries].[iso_code]
                              WHERE
                                    [dbo].[Resorts].[id] /* pk */
                              IN
                              (
                                    SELECT
                                          *
                                    FROM
                                          #tmpIn
                                    --already ordered and cannot add ordering to view
                              )
                              ORDER BY
                                    [dbo].[Resorts].[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
                  END
                  
                  -- clean up
                  IF OBJECT_ID('tempdb..#tmpIn') IS NOT NULL
                        DROP TABLE #tmpIn
                  IF OBJECT_ID('tempdb..#tmpNot') IS NOT NULL
                        DROP TABLE #tmpNot

                  -- 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( @country IS NULL )
                  BEGIN

                        IF( @title IS NULL )
                        BEGIN

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

                        END
                        ELSE
                        BEGIN

                              --01
                              -- cursor used for all paging
                              DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
                              SELECT
                                    [dbo].[Resorts].[id] /* pk */
                              FROM
                                    [dbo].[Resorts]
                              WHERE
                                    [dbo].[Resorts].[title] = @title
                              ORDER BY
                                    [dbo].[Resorts].[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].[SelectPagedResortsBy]''', 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].[SelectPagedResortsBy]''', 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].[Resorts].[id], /* pk */
                                    [dbo].[Resorts].[createdDate],
                                    [dbo].[Resorts].[country], /* fk */
                                    [dbo].[Countries].[name] AS [CountryName], /* fk name column */
                                    [dbo].[Resorts].[title],
                                    [dbo].[Resorts].[shortDescription],
                                    [dbo].[Resorts].[htmlDescription],
                                    [dbo].[Resorts].[htmlMountainFacts]
                              FROM
                                    [dbo].[Resorts]
                                    INNER JOIN [dbo].[Countries] ON [dbo].[Resorts].[country] = [dbo].[Countries].[iso_code]
                              INNER JOIN @PrimaryKeyTable AS pkTable ON
                                    [dbo].[Resorts].[id] /* pk */
                                      = pkTable.PK1
                              ORDER BY
                                    [dbo].[Resorts].[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
                  END
                  ELSE
                  BEGIN

                        IF( @title IS NULL )
                        BEGIN

                              --10
                              -- cursor used for all paging
                              DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
                              SELECT
                                    [dbo].[Resorts].[id] /* pk */
                              FROM
                                    [dbo].[Resorts]
                              WHERE
                                    [dbo].[Resorts].[country] = @country
                              ORDER BY
                                    [dbo].[Resorts].[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].[SelectPagedResortsBy]''', 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].[SelectPagedResortsBy]''', 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].[Resorts].[id], /* pk */
                                    [dbo].[Resorts].[createdDate],
                                    [dbo].[Resorts].[country], /* fk */
                                    [dbo].[Countries].[name] AS [CountryName], /* fk name column */
                                    [dbo].[Resorts].[title],
                                    [dbo].[Resorts].[shortDescription],
                                    [dbo].[Resorts].[htmlDescription],
                                    [dbo].[Resorts].[htmlMountainFacts]
                              FROM
                                    [dbo].[Resorts]
                                    INNER JOIN [dbo].[Countries] ON [dbo].[Resorts].[country] = [dbo].[Countries].[iso_code]
                              INNER JOIN @PrimaryKeyTable AS pkTable ON
                                    [dbo].[Resorts].[id] /* pk */
                                      = pkTable.PK1
                              ORDER BY
                                    [dbo].[Resorts].[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
                        ELSE
                        BEGIN

                              --11
                              -- cursor used for all paging
                              DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
                              SELECT
                                    [dbo].[Resorts].[id] /* pk */
                              FROM
                                    [dbo].[Resorts]
                              WHERE
                                    [dbo].[Resorts].[country] = @country
                                    AND [dbo].[Resorts].[title] = @title
                              ORDER BY
                                    [dbo].[Resorts].[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].[SelectPagedResortsBy]''', 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].[SelectPagedResortsBy]''', 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].[Resorts].[id], /* pk */
                                    [dbo].[Resorts].[createdDate],
                                    [dbo].[Resorts].[country], /* fk */
                                    [dbo].[Countries].[name] AS [CountryName], /* fk name column */
                                    [dbo].[Resorts].[title],
                                    [dbo].[Resorts].[shortDescription],
                                    [dbo].[Resorts].[htmlDescription],
                                    [dbo].[Resorts].[htmlMountainFacts]
                              FROM
                                    [dbo].[Resorts]
                                    INNER JOIN [dbo].[Countries] ON [dbo].[Resorts].[country] = [dbo].[Countries].[iso_code]
                              INNER JOIN @PrimaryKeyTable AS pkTable ON
                                    [dbo].[Resorts].[id] /* pk */
                                      = pkTable.PK1
                              ORDER BY
                                    [dbo].[Resorts].[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
                  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].[SelectPagedResortsBy]''', 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].[SelectPagedResortsBy]''', 10, 1 )
            RETURN -999 -- standardised return code for this - 'Unexpected rowcount'      
      END       
      
      ----------------------------------------------------
      -- Return total number of records
      ----------------------------------------------------  
      SELECT
            COUNT(*) AS [RecordCount]
      FROM
            [dbo].[Resorts]
            
      /* 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].[SelectPagedResortsBy]''  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

-----------------------------------------------------
smaccaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pai_prasadCommented:
I changed

SELECT
                              [dbo].[Resorts].[id] /* pk */
                         INTO
                              #tmpNot
                         FROM
                              [dbo].[Resorts]
                         WHERE
                              [dbo].[Resorts].[title] = @title
                         ORDER BY
                              [dbo].[Resorts].[id] /* pk */


to


Insert into #tmpNot
Select......

it works...
for each of the insert #temp table...
atleast it compiles...
0
MikeWalshCommented:
Well that would compile because the syntax is fine, but it would not work if those tables Don't exist yet.

The reason this doesn't work is when the local Temporary table is created it is created with a unique string at the end of it.. Create a temp table then do a select on the TempDB sysobjects table (select * from tempDB.DBO.SysObjects)

You will see your tempDB but it will have a long string after it like this "___________________________00000291". This is because you can have many local temp tables between the different connections using your application, and each one is a local table to that session so the events of other sessions don't affect it.

If you are looking for a global solution use ## instead of # and then the name will be exactly as you expect it in Sysobjects of the TempDB (look it up by creating it and then querying the table again).

0
MikeWalshCommented:
Of course that only applies when searching for the object ID.. You can still issue a DROP TABLE #tmpIn and it is going to drop your #tmpIn.. SQL knows which session you are coming from. If there is a potential for many users to have these tables you don't want to do a like comparison to just drop the table because it could be the wrong one. You also don't want to use a global temp (## in front).. So what you could do is just drop it anyway and code accordingly if you get the message saying it doesn't exist... Or you could use a global table, or you could use a Table Variable within the session (look it up Table Variables in Books Online for more)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jedimikeCommented:

If you're bent on local temps, use the "exists" function to see if the table exists.   The is null is not full proof.



Try using global temp (##) tables..  and do your drop this way.

if exists (SELECT 'x' FROM tempdb..sysobjects WHERE type = 'U' and NAME = '##tempstack')
drop table [dbo].[##tempstack]
0
MikeWalshCommented:
Thanks for agreeing with me JediMike about the Global Temp Tables.. This is pretty much what I said about the global temp tables. Here the problem is not the Null problem, so exists would not help, because the local temp tables are named in a certain way. So you would need to use a global temp, but if  this is a solution used concurrently by more than 1 user, you will be in trouble because each user needs their own temp table, and a global temp table defeats that by making it a global temp.

So the workaround here would be to use Table variables.
0
smaccaAuthor Commented:
Can you provide examples of the suggested implementation(s)?
I cannot use a global temp table as users will be accessing the data from disparate, concurrent web clients.
0
MikeWalshCommented:
Books Online has a good discussion of Table Variables

here are some examples showing syntaxes:
http://www.aspfaq.com/show.asp?id=2475


You just declare this variable like a normal SQL Variable, except you can give it the table structure and then you can insert into the variable.. See my little example below. Please note you can't do select into with a table variable, so you need to change your syntax to first create the table with all the column definitions, and then INSERT INTO the variable. Then you can select from the variable just like it were a temp table:

This just takes a few columns from sysobjects as a table, inserts into the table variable and selects from it so you can understnad the syntax. You should be able to apply this to your script once you understand how it is working, and I think this illustrates that.

DECLARE @tableVariable TABLE
(name varchar(50),xtype varchar(25), uid int)

INSERT INTO @tableVariable
  SELECT name,xtype,uid
    FROM sysobjects      

select * FROM @tableVariable
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
Mike,

For the record, I explained all of this to the questioner over two months ago:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21696982.html#15707733

I hope you have better luck :)
0
MikeWalshCommented:
Thanks at least I can try anyway. It sounds like in this case table variables will help best. I have been away from answering EE questions for a bit, I forgot to check previous questions (and previous still open questions). Oh well at least we can try.
0
Anthony PerkinsCommented:
>>Thanks at least I can try anyway. <<
You are doing a fine job :)
0
MikeWalshCommented:
Smacca - where are you with this?
0
smaccaAuthor Commented:
Thanks for all your input - I really appreciate it.
Regarding previous question, I didnt quite get the response I was looking for so I reposted as the question had gone STALE!

Thanks again for your time.
0
MikeWalshCommented:
No problem, but did this make sense and help you? I don't want to see you throw an accept on it if you are still stuck.
0
smaccaAuthor Commented:
All cool. I knew table variables and temp tables would work if I defined them explcitly.
Just thought, there would be an easy way to drop an implicitly defined table such as when you use a statement like:

    "SELECT INTO #tmpTable"

Thanks for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.