求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
最佳答案
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
其他回答(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就这两种情况,可以看情况修改上面存储过程
