求sql存储过程怎么修改

Question

功能是根据ProductsId从Base_Gx_ProductsInfo表中获取ProductsTypeId,Sales的值，然后根据获取到的ProductsTypeId，在从表Base_SmallTypeInfo获取SaleValue进行比较。如果Sales小于SaleValue则更新表update Base_Gx_ProductsInfo set Status= 3 ,ProcessStatus=1
  Where ProductsId=@ProductsId
否则更新为 Status= 1 ,ProcessStatus=1，然后再比较下面的
ALTER PROCEDURE [dbo].[Check_productStatus]
@ProductsId int,
@out_msg nvarchar(20) output,
@out_result int output
 AS 
 BEGIN

  /*变量定义*/
  DECLARE
  @VCOUNT INT, 
  @VCARDSN INT, 
  @VOPCOUNT INT, 
  @VProductsTypeId INT, 
@VSales VARCHAR(30),
@VSaleValue VARCHAR(30), 
  @VEMPCODE VARCHAR(30) 
 SELECT ProductsTypeId,Sales
  into VProductsTypeId
FROM [Base_Gx_ProductsInfo]
WHERE ProductsId=@ProductsId;
  SELECT SaleValue
  into VSaleValue
FROM Base_SmallTypeInfo
WHERE ProductsTypeId=@VProductsTypeId;
IF convert(int,@VSales)>convert(int,@VSaleValue)
BEGIN
  update Base_Gx_ProductsInfo set Status= 3 ,ProcessStatus=1
  Where ProductsId=@ProductsId
  SET @out_msg = '更新状态成功'
  SET @out_result = 101 
 END
Else
SELECT @VCOUNT = ISNULL(COUNT_BIG(*), 0)
FROM [Base_Gx_ProductsInfo]
WHERE ProductsId=@ProductsId and ProcessStatus<>1;
 if @VCOUNT > 0
  BEGIN
   
update Base_Gx_ProductsInfo set Status= 3 ,ProcessStatus=1
  Where ProductsId=@ProductsId

update Base_Gx_ProductsInfo set Base_Gx_ProductsInfo.ProcessStatus=1, Base_Gx_ProductsInfo.Status = case when b.ProductsName is null then 0 else 1 end from Base_Gx_ProductsInfo as a
inner join Base_CustomerInfo as c on a.customerId=c.customerId
left join Base_State_ProductsInfo as b on a.ProductsName=b.ProductsName and c.CompanyName=b.CompanyName
  Where A.ProductsId=@ProductsId
  SET @out_msg = '提交成功！'
  SET @out_result = 100  
  END
ELSE
 BEGIN
  SET @out_msg = '未找到记录或产品已提交过！'
  SET @out_result = 99 
 END
End

Answer 1

alter proc check_productstate@productsId int  as begindeclare @sales int,declare @salesvalue int select productsTypeId,@sales=Sales from [Base_Gx_ProductsInfo]where productsTypeId=@productsId select @salesvalue=SaleValue from [@salesvalue ]  where  '条件' if @sales < @salesvalue  begin    update Base_Gx_ProductsInfo set Status=3,ProcessStatus=1     Where ProductsId=@ProductsId         if(@@error<>0) goto cleanup endelse begin    update Base_Gx_ProductsInfo set Status=1,ProcessStatus=1     Where ProductsId=@ProductsId         if(@@error<>0) goto cleanup end   if( @@error <> 0 ) goto cleanupelse commit transactionend ---------------------cleanup: rellback transaction   呵呵，乱写一下，不知道对你有没有用。。