[烛光生存狂]-存储过程之一

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Ms_SpInvTrans]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[Ms_SpInvTrans]

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS OFF

--编辑：从ERP导入商品信息

CREATE PROCEDURE Ms_SpInvTrans

(

@TypeID INT = 1

)

DECLARE @Value VARCHAR(50)

DECLARE @Sql VARCHAR(8000)

SELECT @Value = [Value] FROM [Pub_Option] WHERE [Key]='MsUFData'

--增加新的商品

IF @TypeID = 1

BEGIN

--导入类型

SET @Sql = '

INSERT INTO Ms_InvType(InvTypeCode, InvTypeName)

SELECT

cInvCCode AS InvTypeCode

,cInvCName AS InvTypeName

FROM ' + @Value + 'InventoryClass A

WHERE A.cInvCCode NOT IN(SELECT DISTINCT InvTypeCode From Ms_InvType)'

EXEC(@Sql)

--导入商品

SET @Sql = '

INSERT INTO Ms_Inv(InvCode, InvAddCode, InvName, InvStd, Unit, InvTypeCode,Sign)

SELECT

A.cInvCode --ERP编码

,A.cInvAddCode

,A.CinvName AS InvName--存货名称

,A.cInvStd AS InvStd--规格型号

,C.cComUnitName AS Unit --计量单位

,A.cInvCCode

,A.cInvDefine1

FROM ' + @Value + 'Inventory A

LEFT JOIN ' + @Value + 'ComputationUnit C ON A.cComUnitCode = C.cComUnitCode

WHERE A.cInvCode NOT IN(SELECT DISTINCT InvCode From Ms_Inv)'

EXEC(@Sql)

END

ELSE

BEGIN

--刷新类型

SET @Sql = '

UPDATE A SET

A.InvTypeName = B.cInvCName

FROM Ms_InvType A

INNER JOIN ' + @Value + 'InventoryClass B ON A.InvTypeCode = B.cInvCCode'

EXEC(@Sql)

--刷新商品

SET @Sql = '

UPDATE A SET

A.InvAddCode = B.cInvAddCode

,A.InvName = B.CinvName

,A.InvStd = B.cInvStd

,A.Unit = C.cComUnitName

,A.InvTypeCode = B.cInvCCode

,A.Sign= B.cInvDefine1

FROM Ms_Inv A

INNER JOIN ' + @Value + 'Inventory B ON A.InvCode = B.cInvCode

LEFT JOIN ' + @Value + 'ComputationUnit C ON B.cComUnitCode = C.cComUnitCode'

EXEC(@Sql)

END

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

新用户注册返回首页
首页上一页下一页尾页页次1/1 转到第页