时间戳

首页 > 解决方案 > 如何让这个存储过程使用 JSON 检查？

sql - 如何让这个存储过程使用 JSON 检查？

问题描述

我正在考虑使用存储过程来帮助解决我必须更新/插入大约 1000 条记录的情况。有人建议我使用MERGE表值参数来实现这一点，但问题是其中一列是 JSON 字符串。

项目表

id -PK
BrandId- int (FK)
LocationId- int (FK)
Field3 - nvarchar(Max) Json string containing a jsonKey called itemNumber


select * 
from ItemsTbl 
where BrandId = 1 
  and LocationId = 1 
  and JSON_VALUE('Field3',$.itemNumber) = 12345

现在存储过程（对我来说几乎是全新的）现在看起来像这样：

/* Create a table type. */  
CREATE TYPE SourceTableType AS TABLE   
( BrandId INT  
, LocationId INT
, ItemNumber INT
, ...
);  
GO 

CREATE PROCEDURE dbo.usp_InsertTvp  
    @Source SourceTableType READONLY  
AS        
    MERGE INTO Table1 AS Target  
    USING @Source As Source ON Target.BrandId = Source.BrandId 
                            AND Target.LocationId = Source.LocationId 
                            AND Target.ItemNumber = Source.ItemNumber  

    WHEN MATCHED THEN  
        UPDATE SET OtherParam = Source.OtherParam  

    WHEN NOT MATCHED BY TARGET THEN  
        INSERT (BrandId, LocationId, ItemNumber, OtherParam) 
        VALUES (BrandId, LocationId, ItemNumber, OtherParam) ;

问题是这现在似乎没有考虑到ItemNumberJSON 字符串内部而不是它自己的列。所以我认为这行不通

 Target.ItemNumber = Source.ItemNumber

另外我猜SourceTableType必须将其Field3作为参数，然后自行提取出来？

标签： sqljsonsql-serverstored-proceduressql-server-2017

解决方案

我希望我理解你的正确。请试试这个：

;WITH MergeTarget AS (
    SELECT t.BrandId,t.LocationId,t.Field3,JSON_VALUE(t.Field3,'$.itemNumber') AS [ItemNumber],t.OtherParam
    FROM Table1 AS t
)
MERGE MergeTarget AS target
USING (
    SELECT s.BrandId,s.LocationId,s.ItemNumber,'{"itemNumber":"'+CONVERT(NVARCHAR(255),s.ItemNumber)+'"' AS [Field3],s.OtherParam
    FROM @Source AS s
) AS source ON source.BrandId = target.BrandId
    AND source.LocationId = target.LocationId
    AND source.ItemNumber = target.ItemNumber
WHEN MATCHED AND target.OtherParam <> source.OtherParam THEN UPDATE SET target.OtherParam = source.OtherParam
WHEN NOT MATCHED THEN INSERT (BrandId, LocationId, Field3, OtherParam) 
    VALUES(source.BrandId,source.LocationId,source.Field3,source.OtherParam)
;

有任何问题也请告诉我。

推荐阅读