以下存储过程有什么问题？

alter procedure product_sales 
@productid int,
@quantitysell int
as 
begin
    declare @productname varchar(20)
    declare @quantityavailable int 

    select @quantityavailable=quantityav from products_trycatch 
    select @productname=productname from products_trycatch where productid=@productid
    select @productid=productid from products_trycatch

    if(not exists(select @productid from products_trycatch)
        begin
            raiserror('Product does not exist',16,1)
        end 
    else
        begin
            if (@quantitysell>@quantityavailable)
                begin 
                    print 'Stock not available'
                end 
            else 
                begin 
-------------
                update products_trycatch set quantityav=quantityav-@quantitysell
                insert into product_log values(@productid,@productname,@quantitysell)
             end

Please let me know where the mistake is. What i am trying to do is. one table contains stock available for a product, when i execute the SP i mentioned which product and how many quantity is being sold, The sp deducts that value from the available quantity table and updates in a new table the quantity sold.

请告诉我错误的地方。我想做的是。一个表包含产品可用的库存,当我执行SP我提到哪个产品和销售数量时,sp从可用数量表中扣除该值并在新表中更新销售数量。

5 个解决方案

#1

You're thinking very procedurally, laying things out step by step, but missing parts out from several of your steps.

你正在考虑程序性,一步一步地解决问题,但是从你的几个步骤中遗漏了部分内容。

Don't write several queries to retrieve individual pieces of data. Think about the problem as a whole¹:

不要写几个查询来检索单个数据。从整体上考虑问题1:

alter procedure product_sales 
@productid int,
@quantitysell int
as 
begin
declare @rc int
update products_trycatch
set quantityav = quantityav - @quantitysell
where productid = @productid and quantityav > @quantitysell
set @rc = @@ROWCOUNT
if @rc = 1
begin
    insert into product_log
    select @productid,productname,@quantitysell
    from product_trycatch
    where productid = @productid
end
else
begin
  if not exists(select * from products_trycatch where productid = @productid)
  begin
    raiserror('Product does not exist',16,1)
  end
  else
  begin
    print 'Stock not available'
  end
end

Not how all of my queries target the row in product_trycatch with the matching productid column - several of yours don't which means that they're going to be assigning non-deterministic values to your variables, or updating all rows in product_trycatch and deducting the @quantitysell value.

不是我的所有查询都使用匹配的productid列来定位product_trycatch中的行 - 您的几个不会意味着他们将为您的变量分配非确定性值,或者更新product_trycatch中的所有行并扣除@quantitysell值。

¹For bonus points we could write a single update with case and an output clause that would eliminate the need to perform any re-querying of the product_trycatch table for the product name or in the failure path, but it's probably not worth the additional complexity here. This query shows the general technique:

1对于奖励积分,我们可以使用大小写和输出子句编写单个更新,从而无需对product_trycatch表执行产品名称或失败路径的任何重新查询,但这可能不值得额外的复杂性。此查询显示了一般技术:

declare @products table (ID int not null, Quantity int not null, Name varchar(20) not null)
insert into @products (ID,Quantity,Name) values (1,15,'Fred')

declare @ID int
declare @Qty int
declare @RefData table (OldQuantity int not null, NewQuantity int not null,
                        Name varchar(20) not null)

select @ID = 1, @Qty = 20

update @products
set Quantity = CASE WHEN Quantity >= @Qty THEN Quantity - @Qty ELSE Quantity END
output deleted.Quantity,inserted.Quantity, inserted.Name into @RefData
where ID = @ID

select * from @RefData

where you can play with the @ID and @Qty values to see the various outcomes reflected in @RefData when the quantity requested is higher than that which is available or where the product does or doesn't exist.

当请求数量高于可用数量或产品存在或不存在时,可以使用@ID和@Qty值来查看@RefData中反映的各种结果。

#2

Formatted your code and added missing words -

格式化您的代码并添加遗漏的单词 -

alter procedure product_sales 
@productid int,
@quantitysell int
as 
begin
    declare @productname varchar(20)
    declare @quantityavailable int 
    select @quantityavailable=quantityav from products_trycatch 
    select @productname=productname from products_trycatch where productid=@productid
    select @productid=productid from products_trycatch
    if not exists(select @productid from products_trycatch)
    begin
        raiserror('Product does not exist',16,1)
    end 
    else if (@quantitysell>@quantityavailable)
    begin 
        print 'Stock not available'
    end
    else 
    begin 
        -------------
        update products_trycatch set quantityav=quantityav-@quantitysell
        insert into product_log values(@productid,@productname,@quantitysell)
    end
end

#3

Here is the Formatted code

这是格式化代码

    alter procedure product_sales 
@productid int,
@quantitysell int
as 
begin
declare @productname varchar(20)
declare @quantityavailable int 
select @quantityavailable=quantityav from products_trycatch 
select @productname=productname from products_trycatch where productid=@productid
select @productid=productid from products_trycatch
if(not exists(select @productid from products_trycatch))
begin
raiserror('Product does not exist',16,1)
end 
else if (@quantitysell>@quantityavailable)
begin 
print 'Stock not available'
end 
else 
begin 
-------------
update products_trycatch set quantityav=quantityav-@quantitysell
insert into product_log values(@productid,@productname,@quantitysell)
end 
end

#4

Just Modified your Stored Proc have a look at it

刚刚修改过您的存储过程看看它

ALTER PROCEDURE Product_sales 
(@Productid    INT, 
 @Quantitysell INT
 ) 
AS 
  BEGIN TRY 
      DECLARE @Productname VARCHAR(20) 
      DECLARE @Quantityavailable INT 

      SELECT @Quantityavailable = Quantityav 
      FROM   Products_trycatch 

      SELECT @Productname = Productname 
      FROM   Products_trycatch 
      WHERE  Productid = @Productid 

      SELECT @Productid = Productid 
      FROM   Products_trycatch 

      IF( @Productid IS NULL ) 
        BEGIN 
            RAISERROR('Product does not exist',16,1) 
        END 
      ELSE IF ( @Quantitysell > @Quantityavailable ) 
        BEGIN 
            PRINT 'Stock not available' 
        END 
      ELSE 
        BEGIN 
            DECLARE @Is_transcount INT 

            BEGIN TRANSACTION 

            SET @Is_transcount=1 

            UPDATE Products_trycatch 
            SET    Quantityav = Quantityav - @Quantitysell; 

            INSERT INTO Product_log 
            VALUES     (@Productid, 
                        @Productname, 
                        @Quantitysell) 

            COMMIT TRANSACTION 

            SET @Is_transcount=0 
        END 
  END TRY 

  BEGIN CATCH 
      IF( @@Trancount > 0 ) 
        ROLLBACK TRANSACTION 

      DECLARE @Error_message   VARCHAR(Max), 
              @Error_number    INT, 
              @Error_procedure VARCHAR(100) 

      SELECT @Error_message = ERROR_MESSAGE(), 
             @Error_number = ERROR_NUMBER(), 
             @Error_procedure = ERROR_PROCEDURE() 
  END CATCH

#5

The SP is open to a Parameter Sniffing issue.

SP对参数嗅探问题持开放态度。

You re-assign the value of @productid part way through your code.

您可以通过代码重新分配@productid的值。

From this point forward SQL will be making more assumptions about what plan to use because it no longer knows the actual value.

从这一点开始,SQL将对使用什么计划做出更多假设,因为它不再知道实际价值。

#1