Must declare the scalar variable "@IDAlbum"

Question

I am trying to create a simple website using ASP.net 2010 and SQL server. I am blocked by the error "Must declare the scalar variable "@IDAlbum" after calling the procedure Pager (see the code below). Can someone help me resolve this pbm please?

 USE [Portfolio]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Pager]
    (@PageNo int = 1,
    @ItemsPerPage int = 2,
    @IDAlbum int,
    @TotalRows int out)
AS
BEGIN
  SET NOCOUNT ON
  DECLARE
    @StartIdx int,
    @SQL nvarchar(max),  
    @SQL_Conditions nvarchar(max),
    @EndIdx int

    IF @PageNo < 1 SET @PageNo = 1
    IF @ItemsPerPage < 1 SET @ItemsPerPage = 10

    SET @StartIdx = (@PageNo -1) * @ItemsPerPage + 1
    SET @EndIdx = (@StartIdx + @ItemsPerPage) - 1
    SET @SQL = 'SELECT picture
                FROM (
                SELECT  ROW_NUMBER() OVER(ORDER BY IDAlbum) AS Row, *  
                      FROM  Album_Pic WHERE IDAlbum =  @IDAlbum )
                      AS tbl WHERE  
                      Row >= ' + CONVERT(varchar(9), @StartIdx) + 
                      ' AND Row <=  ' + CONVERT(varchar(9), @EndIdx) 



    EXEC sp_executesql @SQL

    SET @SQL = 'SELECT @TotalRows=COUNT(*) FROM Album_Pic WHERE IDAlbum = ' + CONVERT(varchar(9), @IDAlbum) 
    EXEC sp_executesql 
        @query = @SQL, 
        @params = N'@TotalRows INT OUTPUT', 
        @TotalRows = @TotalRows OUTPUT 
END

Answer 1

@idAlbum was out the scope of dynamic query.

 USE [Portfolio]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Pager]
    (@PageNo int = 1,
    @ItemsPerPage int = 2,
    @IDAlbum int,
    @TotalRows int out)
AS
BEGIN
  SET NOCOUNT ON
  DECLARE
    @StartIdx int,
    @SQL nvarchar(max),  
    @SQL_Conditions nvarchar(max),
    @EndIdx int

    IF @PageNo < 1 SET @PageNo = 1
    IF @ItemsPerPage < 1 SET @ItemsPerPage = 10

    SET @StartIdx = (@PageNo -1) * @ItemsPerPage + 1
    SET @EndIdx = (@StartIdx + @ItemsPerPage) - 1
    SET @SQL = 'SELECT picture
                FROM (
                SELECT  ROW_NUMBER() OVER(ORDER BY IDAlbum) AS Row, *  
                      FROM  Album_Pic WHERE IDAlbum =  ' +convert(varchar(10),@IDAlbum)+' )
                      AS tbl WHERE  
                      Row >= ' + CONVERT(varchar(9), @StartIdx) + 
                      ' AND Row <=  ' + CONVERT(varchar(9), @EndIdx) 



    EXEC sp_executesql @SQL

    SET @SQL = 'SELECT @TotalRows=COUNT(*) FROM Album_Pic WHERE IDAlbum = ' + CONVERT(varchar(9), @IDAlbum) 
    EXEC sp_executesql 
        @query = @SQL, 
        @params = N'@TotalRows INT OUTPUT', 
        @TotalRows = @TotalRows OUTPUT 
END