Sql Help Must declare the scalar variable "@TotalRecords".

JRockFL
JRockFL used Ask the Experts™
on
I'm getting this error. Must declare the scalar variable "@TotalRecords".

Has to do with this line..
@TotalRecords AS TotalRecords,

When I hard code @TotalRecords, it works.

ALTER PROCEDURE [dbo].[GetECommerceItems]
      @PageIndex int,
      @NumRows int,
      @Vendor varchar(50) = null,
      @ItemNo varchar(50) = null,
      @Category varchar(50) = null,
      @Sort varchar(100) = null
AS

DECLARE @TotalRecords int
DECLARE @StartRowIndex int

SET @StartRowIndex  = (@PageIndex * @NumRows) + 1

SELECT @TotalRecords =
(
SELECT COUNT(*)
FROM
      aspnet_webItems
WHERE
      Ecommerce = 'Y'      
AND
      (@Vendor IS NULL OR Vendor = @Vendor)
AND
      (@ItemNo IS NULL OR Item_No = @ItemNo)            
AND
      (@Category IS NULL OR Category = @Category)      
)

SELECT
      IDENTITY(int, 1,1) AS Row,
      Item_No,
      Vendor,
      Memo_Desc,
      [Desc],
      imgProduct
INTO #OrderRows      
FROM
      aspnet_webItems      
WHERE
      Ecommerce = 'Y'      
AND
      (@Vendor IS NULL OR Vendor = @Vendor)
AND
      (@ItemNo IS NULL OR Item_No = @ItemNo)      
AND
      (@Category IS NULL OR Category = @Category)      

--WITH OrderRows AS
--(
--SELECT
--      ROW_NUMBER() OVER (Order BY Item_No) AS Row,
--      Item_No,
--      Vendor,
--      Memo_Desc,
--      [Desc],
--      imgProduct
--FROM
--      aspnet_webItems      
--WHERE
--      Ecommerce = 'Y'      
--AND
--      (@Vendor IS NULL OR Vendor = @Vendor)
--AND
--      (@ItemNo IS NULL OR Item_No = @ItemNo)                  
--)

DECLARE @SQL AS NVARCHAR(3000)
SET @SQL = '
SELECT
      Item_No,
      Vendor,
      Memo_Desc,
      [Desc],
      @TotalRecords AS TotalRecords,
      imgProduct
FROM
      #OrderRows
WHERE
      Row BETWEEN ' + Convert(varchar(50), @StartRowIndex) + ' AND '  + Convert(varchar(50), @StartRowIndex + @NumRows - 1)      

IF @Sort IS NOT NULL
BEGIN
      SET @SQL = @SQL + ' ORDER BY ' + @Sort
END      
      
exec sp_executesql @SQL
      
DROP TABLE #OrderRows
      
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
This should work:
ALTER PROCEDURE [dbo].[GetECommerceItems]
      @PageIndex int,
      @NumRows int,
      @Vendor varchar(50) = null,
      @ItemNo varchar(50) = null,
      @Category varchar(50) = null,
      @Sort varchar(100) = null
AS

DECLARE @TotalRecords int;
DECLARE @StartRowIndex int;

SET @StartRowIndex  = (@PageIndex * @NumRows) + 1

SELECT @TotalRecords =
(
SELECT COUNT(*)
FROM
      aspnet_webItems
WHERE
      Ecommerce = 'Y'      
AND
      (@Vendor IS NULL OR Vendor = @Vendor)
AND
      (@ItemNo IS NULL OR Item_No = @ItemNo)            
AND
      (@Category IS NULL OR Category = @Category)      
)

SELECT
      IDENTITY(int, 1,1) AS Row,
      Item_No,
      Vendor,
      Memo_Desc,
      [Desc],
      imgProduct
INTO #OrderRows      
FROM
      aspnet_webItems      
WHERE
      Ecommerce = 'Y'      
AND
      (@Vendor IS NULL OR Vendor = @Vendor)
AND
      (@ItemNo IS NULL OR Item_No = @ItemNo)      
AND
      (@Category IS NULL OR Category = @Category)      

--WITH OrderRows AS
--(
--SELECT
--      ROW_NUMBER() OVER (Order BY Item_No) AS Row,
--      Item_No,
--      Vendor,
--      Memo_Desc,
--      [Desc],
--      imgProduct
--FROM
--      aspnet_webItems      
--WHERE
--      Ecommerce = 'Y'      
--AND
--      (@Vendor IS NULL OR Vendor = @Vendor)
--AND
--      (@ItemNo IS NULL OR Item_No = @ItemNo)                  
--)

DECLARE @SQL AS NVARCHAR(3000)
SET @SQL = '
SELECT
      Item_No,
      Vendor,
      Memo_Desc,
      [Desc],
      @TotalRecords AS TotalRecords,
      imgProduct
FROM
      #OrderRows
WHERE
      Row BETWEEN ' + Convert(varchar(50), @StartRowIndex) + ' AND '  + Convert(varchar(50), @StartRowIndex + @NumRows - 1)      

IF @Sort IS NOT NULL
BEGIN
      SET @SQL = @SQL + ' ORDER BY ' + @Sort
END      
      
exec sp_executesql @SQL
      
DROP TABLE #OrderRows

Open in new window

Author

Commented:
Results are the same. I cant see what you changed.
Top Expert 2012
Commented:
>>Must declare the scalar variable "@TotalRecords"<<
That is because you have not declared @TotalRecords.

Instead of this:
exec sp_executesql @SQL

Do this:
exec sp_executesql @SQL, N'@TotalRecords int', @TotalRecords = @TotalRecords

Top Expert 2012

Commented:
In fact I would take it a step further and write it like this:
DECLARE @SQL AS NVARCHAR(3000)
SET @SQL = '
SELECT 
      Item_No,
      Vendor,
      Memo_Desc,
      [Desc],
      @TotalRecords AS TotalRecords,
      imgProduct
FROM
      #OrderRows 
WHERE
      Row BETWEEN @StartRowIndex AND @StartRowIndex + @NumRows - 1'

IF @Sort IS NOT NULL
BEGIN
      SET @SQL = @SQL + ' ORDER BY ' + @Sort
END      
      
exec sp_executesql @SQL, N'@TotalRecords int, @StartRowIndex int, @NumRows int',
					@TotalRecords = @TotalRecords,
					@StartRowIndex = @StartRowIndex,
					@NumRows = @NumRows

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial