Must declare the scalar variable "@ProductID".

Question

Hi all

I am trying to implement an image gallery into my project by using an example downloaded from ASP Snippts...

This particular gallery gets all images to display on an image page, and displays very effective zoomed images on button_click, similar to lightbox gallery solutions.

However, I do not want to display all images, only images related to a particular selected product! I have created another very simple sp which gets images by ProductID but it also stops other functions working such as the paging feature.

So I want to tweak the supplied sp to only get products images by a supplied ProductID... but for some reason I am getting the error 'Must declare the scalar variable "@ProductID".' when I try to execute the sp in SQL Server 08.

Can someone please tell me how to declare a scalar variable please? There is a lot of info online but nothing seams to be working!!

GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spx_Pager]
	@PageNo int = 1,
	@ItemsPerPage int = 2,
	@TotalRows int out,
	@ProductID int
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 FilePath
                FROM (
                SELECT  ROW_NUMBER() OVER(ORDER BY ID) AS Row, * 
                      FROM  tblFiles ) AS tbl WHERE  Row >= ' 
						+ CONVERT(varchar(9), @StartIdx) + ' AND
                       Row <=  ' + CONVERT(varchar(9), @EndIdx) + 'AND ID = @ProductID'
	EXEC sp_executesql @SQL

	SET @SQL = 'SELECT @TotalRows=COUNT(*) FROM tblFiles' 
	EXEC sp_executesql 
        @query = @SQL, 
        @params = N'@TotalRows INT OUTPUT', 
        @TotalRows = @TotalRows OUTPUT 
END

Msg 137, Level 15, State 2, Line 5
Must declare the scalar variable "@ProductID".

(1 row(s) affected)

(1 row(s) affected)

Thank you! :)

Answer 1

Hi ,

try this

GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spx_Pager]
	@PageNo int = 1,
	@ItemsPerPage int = 2,
	@TotalRows int out,
	@ProductID int
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 FilePath
                FROM (
                SELECT  ROW_NUMBER() OVER(ORDER BY ID) AS Row, * 
                      FROM  tblFiles ) AS tbl WHERE  Row >= ' 
						+ CONVERT(varchar(9), @StartIdx) + ' AND
                       Row <=  ' + CONVERT(varchar(9), @EndIdx) + 'AND ID ='+ CONVERT(Varchar(9),@ProductID)
	EXEC sp_executesql @SQL
	SET @SQL = 'SELECT @TotalRows=COUNT(*) FROM tblFiles' 
	EXEC sp_executesql 
        @query = @SQL, 
        @params = N'@TotalRows INT OUTPUT', 
        @TotalRows = @TotalRows OUTPUT 
END

---

Thanks & Regards, sathya

Answer 2

The sp_execute_sql has different scope than the the SP and hence the variables declared in the SP wont be visible to the sp_execute_sql

try this way

SET @SQL = 'SELECT FilePath
                FROM (
                SELECT  ROW_NUMBER() OVER(ORDER BY ID) AS Row, * 
                      FROM  tblFiles ) AS tbl WHERE  Row >= ' 
						+ CONVERT(varchar(9), @StartIdx) + ' AND
                       Row <=  ' + CONVERT(varchar(9), @EndIdx) + 'AND ID = ' + cast(@ProductID as varchar)
	EXEC sp_executesql @SQL

---

Regards
Satheesh

Answer 3

I suggest you use parameterized queries and pass ProductId as a parameter to address the issue:

ALTER PROCEDURE [dbo].[spx_Pager]
	@PageNo int = 1,
	@ItemsPerPage int = 2,
	@TotalRows int out,
	@ProductID int
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 FilePath
                FROM (
                SELECT  ROW_NUMBER() OVER(ORDER BY ID) AS Row, * 
                      FROM  tblFiles ) AS tbl
					  WHERE  Row >= @StartIdx
                       AND Row <= @EndIdx 
					   AND ID = @ProductID;'
	EXEC sp_executesql @SQL
		,N'@StartIdx int, @EndIdx int, @ProductID int'
		,@StartIdx = @StartIdx
		, @EndIdx = @EndIdx
		, @ProductID = @ProductID;

	SET @SQL = 'SELECT @TotalRows=COUNT(*) FROM tblFiles';
	EXEC sp_executesql 
        @query = @SQL, 
        @params = N'@TotalRows INT OUTPUT', 
        @TotalRows = @TotalRows OUTPUT;
END
GO

---

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

Answer 4

Since you apparently are not a very experienced SQL programmer, you should
not engage in dynamic SQL, which is a fairly advanced feature, and you are
not using it correctly. Luckily in this case, there is absolute no reason to
use dynamic SQL at all:

      SET @StartIdx = (@PageNo -1) * @ItemsPerPage + 1
      SET @EndIdx = (@StartIdx + @ItemsPerPage) - 1
      SELECT FilePath
      FROM  (SELECT  ROW_NUMBER() OVER(ORDER BY ID) AS Row, *
             FROM    tblFiles ) AS tbl
      WHERE  Row >= @StartIdx
        AND  Row <= @EndIdx
        AND  ID = @ProductID

Likewise you can replace:

   SET @SQL = 'SELECT @TotalRows=COUNT(*) FROM tblFiles'
   EXEC sp_executesql
        @query = @SQL,
        @params = N'@TotalRows INT OUTPUT',
        @TotalRows = @TotalRows OUTPUT

With

    SELECT @TotalRows=COUNT(*) FROM tblFiles

A lot simpler, isn't it?

---

Erland Sommarskog, SQL Server MVP, [e-mail]