SQL PROCEDURE Problem

[Stelly]

Hi All

I'm getting...

The multi-part identifier Product.ProductID could not be bound

from this...

CREATE PROCEDURE getProdOnCatPromo
(@DescriptionLength INT,
@PageNumber INT,
@ProductsPerPage INT,
@HowManyProducts INT OUTPUT)
AS

DECLARE @products TABLE
(RowNumber INT,
ProductID INT,
Name VARCHAR(50),
Description VARCHAR(MAX),
Price MONEY,
Thumbnail VARCHAR(50),
Large VARCHAR(50),
OnCatalogPromo bit,
OnDepartmentPromo bit)

INSERT INTO @products
SELECT ROW_NUMBER() OVER (ORDER BY Product.ProductID),
ProductID, Name,
SUBSTRING(Description, 1, @DescriptionLength) + '...' AS Description, Price, Thumbnail, Large, OnCatalogPromo, OnDepartmentPromo
FROM @products
WHERE OnCatalogPromo = 1

SELECT @HowManyProducts = COUNT(ProductID) FROM @products

SELECT ProductID, name, Description, Price, Thumbnail, Large, OnCatalogPromo, OnDepartmentPromo
FROM @products
WHERE RowNumber > (@PageNumber - 1)*@ProductsPerPage
AND RowNumber <= @PageNumber*@ProductsPerPage

Any ideas people??

Stelly

 

[pinkaardvark]

INSERT INTO @products
SELECT ROW_NUMBER() OVER (ORDER BY Product.ProductID),
Your referencing product.productID but your referencing a temp table named products not product

also your inserting into and selecting from the same temporary table.