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.com :: Hosted Web Applications 2005' -
4GuysFromRolla.com
--------- --------------------------------------------------------------------------------------------------------------
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