Paste_Image.png
根据TAG 级别更新对应的字段信息
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: ZHANG
-- Create date: 2017-10-19
-- Description: 修改货位时更新入库祥表中的IDD_DepositedPlace字段
-- =============================================
--exec [dbo].[P_IDD_DepositedPlace_Update] 3,'3&3&1','3&3&2'
ALTER PROCEDURE [dbo].[P_IDD_DepositedPlace_Update]
(
@TAG INT,
@OLDNAME VARCHAR(50),
@NEWNAME VARCHAR(50)
)
AS
BEGIN
DECLARE @IDD_DEPOSITEDPLACE VARCHAR(200)
IF @TAG = 1
BEGIN
--SET @IDD_DEPOSITEDPLACE =
-- (
-- SELECT IDD_DepositedPlace
-- FROM dbo.SM_InDepotDetailTab
-- WHERE LEFT(IDD_DepositedPlace,CHARINDEX('&',IDD_DepositedPlace)-1) = @OLDNAME
-- )
UPDATE dbo.SM_InDepotDetailTab
set IDD_DepositedPlace = @NEWNAME + SUBSTRING(IDD_DepositedPlace,CHARINDEX('&',IDD_DepositedPlace),LEN(IDD_DepositedPlace))
WHERE LEFT(IDD_DepositedPlace,CHARINDEX('&',IDD_DepositedPlace)-1) = @OLDNAME
END
IF @TAG = 2
BEGIN
UPDATE dbo.SM_InDepotDetailTab
SET IDD_DepositedPlace = @NEWNAME + '&' +
-- RIGHT(IDD_DepositedPlace,CHARINDEX('&',RIGHT('&',LEFT(IDD_DepositedPlace,CHARINDEX('&',IDD_DepositedPlace)))))
SUBSTRING( SUBSTRING(IDD_DepositedPlace,CHARINDEX('&',IDD_DepositedPlace)+1,LEN(IDD_DepositedPlace)),
CHARINDEX('&',SUBSTRING(IDD_DepositedPlace,CHARINDEX('&',IDD_DepositedPlace)+1,LEN(IDD_DepositedPlace)))+1,
CHARINDEX('[',SUBSTRING(IDD_DepositedPlace,CHARINDEX('&',IDD_DepositedPlace)+1,LEN(IDD_DepositedPlace)))-1
)
where LEFT(IDD_DepositedPlace,CHARINDEX('&',IDD_DepositedPlace))
+ LEFT( SUBSTRING(IDD_DepositedPlace,CHARINDEX('&',IDD_DepositedPlace)+1,LEN(IDD_DepositedPlace)),CHARINDEX('&',SUBSTRING(IDD_DepositedPlace,CHARINDEX('&',IDD_DepositedPlace),LEN(IDD_DepositedPlace)+1)))
= @OLDNAME
END
IF @TAG = 3
BEGIN
UPDATE DBO.SM_InDepotDetailTab
SET IDD_DepositedPlace = @NEWNAME + SUBSTRING(IDD_DepositedPlace,CHARINDEX('[',IDD_DepositedPlace),CHARINDEX(']',IDD_DepositedPlace))
WHERE LEFT(IDD_DepositedPlace,CHARINDEX('[',IDD_DepositedPlace)-1) = @OLDNAME
END
END
Paste_Image.png
