Hi,

Can someone help me as to why the following SQL is cause the error:

   "The multi-part identifier "..." could not be bound."

Thanks.

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

--region [dbo].[SelectPagedAccountsByAccountID]

------------------------------------------------------------------------------------------------------------------------
-- Procedure Name: [dbo].[SelectPagedAccountsByAccountID]
-- Date Generated: Tuesday, 20 February 2007
-- Author:         Stephen McCormack - stephenm@mwebsolutions.com.au
-- Company:        MWeb Solutions Pty Ltd
-- Software:            CodeSmith v4.0.0.0
-- Template:       StoredProcedures.cst
-- Comments:       'Custom Paging in ASP.NET 2.0 with SQL Server 2005' - http://aspnet.4guysfromrolla.com/demos/printPage.aspx?path=/articles/031506-1.aspx
--------- --------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[SelectPagedAccountsByAccountID]

      -- paging parameters
      @PageNumber int = 1 ,
      @PageSize int = 10 ,

      -- index column(s)
      @AccountID int,
      
      -- sort expressions
      @SortBy varchar(50) = 'CreatedDate' ,
      @SortDirection varchar(4) = 'DESC'
      
AS

      -- turn off rows affected
      SET NOCOUNT ON
                  
      --isolation level
      SET TRANSACTION ISOLATION LEVEL READ COMMITTED

      -- system function stores
      DECLARE
            @ErrStatus int,       -- stores error status
            @RowsAffected int      -- stores number of rows affected

      -- parameter validation
      IF (@PageNumber < 1)      SET @PageNumber = 1
      IF (@PageSize < 1)            SET @PageSize = 10

      ----------------------------------------------------
      -- Determine start and end row index.
      ----------------------------------------------------  
      DECLARE
            @StartRowIndex int,       -- the starting row index
            @EndRowIndex int            -- the ending row index
            
      SET @StartRowIndex = (@PageNumber-1) * @PageSize
      SET @EndRowIndex = (@StartRowIndex + @PageSize)-1

      ----------------------------------------------------
      -- Select Paged Set
      ----------------------------------------------------
      SELECT
            [RowNum],
            [dbo].[Accounts].[AccountID], /* pk */
            [dbo].[Accounts].[CustomerID], /* fk */
            [dbo].[Customers].[Name] AS [CustomerName], /* fk name column */
            [dbo].[Accounts].[DiscountAmount],
            [dbo].[Accounts].[DiscountRate],
            [dbo].[Accounts].[DiscountCode],
            [dbo].[Accounts].[Notes],
            [dbo].[Accounts].[CreatedDate],
            [dbo].[Accounts].[CreatedBy],
            [dbo].[Accounts].[ModifiedDate],
            [dbo].[Accounts].[ModifiedBy]
      FROM
      (
            SELECT
                  [dbo].[Accounts].[AccountID], /* pk */
                  [dbo].[Accounts].[CustomerID], /* fk */
                  [dbo].[Customers].[Name] AS [CustomerName], /* fk name column */
                  [dbo].[Accounts].[DiscountAmount],
                  [dbo].[Accounts].[DiscountRate],
                  [dbo].[Accounts].[DiscountCode],
                  [dbo].[Accounts].[Notes],
                  [dbo].[Accounts].[CreatedDate],
                  [dbo].[Accounts].[CreatedBy],
                  [dbo].[Accounts].[ModifiedDate],
                  [dbo].[Accounts].[ModifiedBy]
                  ,
                  ROW_NUMBER() OVER
                  (
                        ORDER BY
                              [dbo].[Accounts].[CreatedDate] DESC
                  ) AS [RowNum]
            FROM
                  [dbo].[Accounts]
                  INNER JOIN [dbo].[Customers] ON [dbo].[Accounts].[CustomerID] = [dbo].[Customers].[CustomerID]
            WHERE
                  [dbo].[Accounts].[AccountID] = @AccountID
      ) AS [PagedSet]
      WHERE
            [RowNum] BETWEEN @StartRowIndex AND @EndRowIndex      
                  

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

      -- ensure correct number of rows affected
      IF @RowsAffected > @PageSize
      BEGIN
            RAISERROR( 'Unexpected number of rows affected by stored procedure ''[dbo].[SelectPagedAccountsByAccountID]''', 10, 1 )
            RETURN -999 -- standardised return code for this - 'Unexpected rowcount'      
      END
      
      ----------------------------------------------------
      -- RecordCount Resultset
      ----------------------------------------------------  
      SELECT
            COUNT(*) AS [RecordCount]
      FROM
            [dbo].[Accounts]
            
      /* must use single statement immediately to store system functions
      as all DML statements, SELECT, IF, PRINT and SET will reset @@error to zero */
      SELECT
            @ErrStatus = @@ERROR

      -- check for errors
      IF @ErrStatus <> 0
      BEGIN
            RAISERROR( 'Error occurred in stored procedure ''[dbo].[SelectPagedAccountsByAccountID]''  when returning the total number of records.', 10, 1 )
            RETURN @ErrStatus
      END

      
      -- turn on rows affected
      SET NOCOUNT OFF

      -- success(0)
      RETURN 0
      
--endregion

GO