首页 新闻 搜索 专区 学院

求sql存储过程怎么修改

0
悬赏园豆:15 [已解决问题] 解决于 2010-11-09 13:58

功能是根据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

孤星赏月的主页 孤星赏月 | 初学一级 | 园豆:125
提问于:2010-11-07 01:34
< >
分享
最佳答案
0

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

 

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

收获园豆:15
wlz0815 | 小虾三级 |园豆:587 | 2010-11-07 16:23
其他回答(1)
0

CREATE PROCEDURE procedurename AS

BEGIN--第一种情况

update A set status=3,ProcessStatus=1  from B where A.ProductsTypeId=B.ProductsTypeId and A.Sales<B.Sales

--第二种 update A set status=1,ProcessStatus=1  from B,C where A.ProductsTypeId=B.ProductsTypeId and A.Sales>=B.Sales  and a.companyid in (select companyid from D where d.companyname=c.companyname)  and a.ProductsName=c.ProductsName and a.ProductsModel=C.ProductsModel

 END

GO

楼主说的如果ProductsModel字段比较的话如果是A中的信息是sa,表B中ProductsModel信息为:sa、sc则也算存在不知道有没有什么规则还是ProductsModel就这两种情况,可以看情况修改上面存储过程

雪莱·亨尼格 | 园豆:524 (小虾三级) | 2010-11-08 10:44
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册