数据库更新 & & []格式字段

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