Products 无效：SQL server 存储过程解决方法

2013-08-06

Products 无效：SQL server 存储过程在数据库中有表Product，数据结构与表变量@Products基本相同，除了无有R

Products 无效：SQL server 存储过程
在数据库中有表Product，数据结构与表变量@Products基本相同，除了无有RowNumber.
完成执行后返回消息：'Products'无效。不明就里。请高手不吝赐教@@！###
SO,Here comes Code


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

大意就是选取PromoFront为'1'的所有Product,并返回@HowmanyProducts SQL?Server 存储数据结构
[解决办法]
加个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
[解决办法]
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=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*@ProductsPerPage
end

[解决办法]

--说，是不是把存储过程建到master库中了？呵呵
USE Database_db
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
            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 * @ProductsPerPAge

GO

热点排行

SQL Server

Products 无效 ：SQL server 存储过程解决方法

Products 无效：SQL server 存储过程解决方法