存储过程中关键字 end 附近的语法不正确

Question

CREATE PROCEDURE M_SelectStockIN @FarmID   int, 
                                 @ItemName varchar(50), 
                                 @InType   varchar(5) 
AS 
  BEGIN 
      select ( Sum(( m.Item_Qty )) - total ) As InStock, 
             * 
      from   (select Sum(d.Use_Qty) as total 
              from   MoryaSales m 
                     inner join DailyFarmEntry d 
                             on m.batchID = d.FarmID 
              where  m.batchID = @FarmID 
                     and d.FarmID = @FarmID 
                     and m.Tr_Type = @InType 
                     and d.In_Type = @InType 
                     and m.Item_Name = @ItemName 
                     and d.ItemName = @ItemName) 
  END 
RETURN 

我需要帮助来修复该错误： 当我们运行上述存储过程时，错误" 语法不正确"出现在关键字 end 附近

Answer 1

这可能有助于：

删除RETURN并在内部查询上放置别名：

CREATE PROCEDURE M_SelectStockIN
    @FarmID int,
    @ItemName varchar(50),
    @InType varchar(5)
AS
BEGIN        
select 
    (sum((m.Item_Qty)) - total) As InStock,
    * 
from( -- marks the start of the inner query
    select 
        sum(d.Use_Qty) as total 
    from MoryaSales m 
    inner join DailyFarmEntry d 
        on m.batchID = d.FarmID 
    where 
        m.batchID = @FarmID 
        and d.FarmID = @FarmID 
        and m.Tr_Type = @InType 
        and d.In_Type = @InType 
        and m.Item_Name = @ItemName 
        and d.ItemName = @ItemName
    )t -- You need to put an alias for this inner query to prevent the error from happening.
END

Answer 2

您缺少表的别名并删除返回

 select (sum((m.Item_Qty)) - total) As InStock,* from
    (select sum(d.Use_Qty) as total from MoryaSales m 
    inner join DailyFarmEntry d on m.batchID = d.FarmID 
    where m.batchID = @FarmID and d.FarmID = @FarmID 
    and m.Tr_Type = @InType and d.In_Type = @InType 
    and m.Item_Name = @ItemName and d.ItemName = @ItemName)d 
                                                           ^