求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 VProductsTypeIdFROM [Base_Gx_ProductsInfo]WHERE ProductsId=@ProductsId;  SELECT SaleValue  into VSaleValueFROM Base_SmallTypeInfoWHERE 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   ENDElseSELECT @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=@ProductsIdupdate 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 ainner join Base_CustomerInfo as c on a.customerId=c.customerIdleft 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     ENDELSE BEGIN  SET @out_msg = '未找到记录或产品已提交过！'  SET @out_result = 99   ENDEnd

Answer 1

alter proc check_productstate
@productsId int
 
as
begin
declare @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
 end
else
 begin
    update Base_Gx_ProductsInfo set Status=1,ProcessStatus=1
     Where ProductsId=@ProductsId
         if(@@error<>0) goto cleanup
 end

 
if( @@error <> 0 ) goto cleanup
else commit transaction
end
---------------------
cleanup:
 rellback transaction

 

呵呵，乱写一下，不知道对你有没有用。。