Products 无效 ：SQL server 存储过程

Question

在数据库中有表Product，数据结构与表变量@Products基本相同，除了无有RowNumber.
完成执行后返回消息：'Products'无效。不明就里。请高手不吝赐教@@！###
SO,Here comes Code
CREATE PROCEDURE CatalogGetOriductsOnFrontPromo
(@DescriptionLength INT
@PageNumber INT,
@ProductsPerPAge INT,
@HowManyProducts INT OUTPUT
)ASDECLARE @Products TABLE
{
RowNUmber INT,
ProductID INT,
Name NVARCHAR(50),
Description NVARCHAR(MAX),
Price MONEY,
Thumbnail NVARCHAR(50),
Image NVARCHAR(50),
PromoFront bit,
PromoDept bit,
Status binary(50)
}INSERT INTO @Products
Select Row_Number() over(Order By Product.ProductID),
ProductID,Name,Case when Len(Description)<=@DescriptionLength THEN Description
ELSE Substring (Description,1,@DescriptionLength)+'...' END
AS Description,Price,Thumbnail,Image,PromoFront,PromoDept,Status
Fromo Product
Where PrmoFront=1Select @HowManyProducts=Count(ProductID) From @ProductsSelect ProductID, Name,Description,Price,Thumbnail,Image,PromoFront,PromoDept
Status
FROM @Products
WHere RowNumber>(@PageNumber-1)*@ProductsPerPage And
RowNumber<=@PageNumber*@PrductsPerPage
大意就是选取PromoFront为'1'的所有Product,并返回@HowmanyProductsSQL Server存储数据结构

Answer 1

加个use 数据库名
go
CREATE PROCEDURE CatalogGetOriductsOnFrontPromo
(@DescriptionLength INT
@PageNumber INT,
@ProductsPerPAge INT,
@HowManyProducts INT OUTPUT
)
 
AS
 
DECLARE @Products TABLE
{--用括号
RowNUmber INT,
ProductID INT,
Name NVARCHAR(50),
Description NVARCHAR(MAX),
Price MONEY,
Thumbnail NVARCHAR(50),
Image NVARCHAR(50),
PromoFront bit,
PromoDept bit,
Status binary(50)
}--用括号
 
INSERT INTO @Products
Select Row_Number() over(Order By Product.ProductID),
ProductID,Name,Case when Len(Description)<=@DescriptionLength THEN Description
ELSE Substring (Description,1,@DescriptionLength)+'...' END
AS Description,Price,Thumbnail,Image,PromoFront,PromoDept,Status
Fromo Product
Where PrmoFront=1
 
Select @HowManyProducts=Count(ProductID) From @Products
 
Select ProductID, Name,Description,Price,Thumbnail,Image,PromoFront,PromoDept
Status
FROM @Products
WHere RowNumber>(@PageNumber-1)*@ProductsPerPage And
RowNumber<=@PageNumber*@PrductsPerPage

Answer 2

try this,CREATE PROCEDURE CatalogGetOriductsOnFrontPromo
(@DescriptionLength int,
 @PageNumber INT,
 @ProductsPerPAge INT,
 @HowManyProducts INT OUTPUT)
AS
begin
DECLARE @Products TABLE
(RowNUmber INT,
 ProductID INT,
 Name NVARCHAR(50),
 Description NVARCHAR(MAX),
 Price MONEY,
 Thumbnail NVARCHAR(50),
 Image NVARCHAR(50),
 PromoFront bit,
 PromoDept bit,
 Status binary(50))
 
INSERT INTO @Products
 Select Row_Number() over(Order By Product.ProductID),
        ProductID,Name,Case when Len(Description)<=@DescriptionLength THEN Description
        ELSE Substring (Description,1,@DescriptionLength)+'...' END
        AS Description,Price,Thumbnail,Image,PromoFront,PromoDept,Status
 From Product
 Where PrmoFront=1Select @HowManyProducts=Count(ProductID) From @Products
 
Select ProductID, Name,Description,Price,Thumbnail,Image,PromoFront,PromoDept,Status
  FROM @Products
  WHere RowNumber>(@PageNumber-1)*@ProductsPerPage And
        RowNumber<=@PageNumber*@ProductsPerPage
end

Answer 3

再不行,在表名Product前加所在的数据库名,如: [数据库名].dbo.Product

Answer 4

--说，是不是把存储过程建到master库中了？呵呵
USE Database_db
GOCREATE PROCEDURE CatalogGetOriductsOnFrontPromo
    (
      @DescriptionLength INT ,
      @PageNumber INT ,
      @ProductsPerPAge INT ,
      @HowManyProducts INT OUTPUT
    )
AS 
    DECLARE @Products TABLE
        (
          RowNUmber INT ,
          ProductID INT ,
          Name NVARCHAR(50) ,
          Description NVARCHAR(MAX) ,
          Price MONEY ,
          Thumbnail NVARCHAR(50) ,
          Image NVARCHAR(50) ,
          PromoFront BIT ,
          PromoDept BIT ,
          Status BINARY(50)
        )    INSERT  INTO @Products
            SELECT  ROW_NUMBER() OVER ( ORDER BY Product.ProductID ) ,
                    ProductID ,
                    Name ,
                    CASE WHEN LEN(Description) <= @DescriptionLength
                         THEN Description
                         ELSE SUBSTRING(Description, 1, @DescriptionLength)
                              + '...'
                    END AS Description ,
                    Price ,
                    Thumbnail ,
                    Image ,
                    PromoFront ,
                    PromoDept ,
                    Status
            FROM    Product
            WHERE   PrmoFront = 1    SELECT  @HowManyProducts = COUNT(ProductID)
    FROM    @Products    SELECT  ProductID ,
            Name ,
            Description ,
            Price ,
            Thumbnail ,
            Image ,
            PromoFront ,
            PromoDept Status
    FROM    @Products
    WHERE   RowNumber > ( @PageNumber - 1 ) * @ProductsPerPage
            AND RowNumber <= @PageNumber * @ProductsPerPAgeGO