I want to use IF EXISTS in my query to check whether an object contains a "seqId" or not. I'm trying to update or insert into a table from OPENJSON. This json object @identObj has an array of objects, some objects may have a "seqId," and some might not. The ones that have a "seqId" should update into the "case_ident_to_inv" table, if the "seqId" does not exist it should insert into that table, the "case_ident_to_inv" table. The output of inserting into the table is a unique seqId. I tried implementing an IF EXISTS statement and I thought the syntax was correct but I'm getting this error:
Incorrect syntax near 'IF'.
DECLARE @identObj NVARCHAR(MAX) = N'[
{
"investigators": [
{"importId": 177, "userId": 120, "invTypeCd": "LI", "seqId": 50},
{"importId": 177, "userId": 124, "invTypeCd": "SI", "seqId": 50},
{"importId": 177, "userId": 120, "invTypeCd": "RV", "seqId": 50},
{"importId": 177, "userId": 123, "invTypeCd": "SI"},
]
}
]';
Data for "case_ident_to_inv" table
+------------+--------+-------------+---------------+-------+
| importId | userId | invTypeCd | caseIdentId | seqId |
+------------+--------+-------------+---------------+-------+
| 177 | 120 | LI | 10200 | 50 |
| 177 | 124 | SI | 10200 | 51 |
| 177 | 120 | RV | 10200 | 52 |
| 177 | 123 | SI | 10200 | |<--- seqId OUTPUT AFTER INSERT
+------------+--------+-------------+---------------+-------+
The caseIdentId and importId will always be the same, there can be identical userId's and invTypeCd's, seqId is always unique.
Stored Procedure
select I.*
INTO #tmpInvs
FROM OPENJSON(@identObj)
WITH (
invs NVARCHAR(MAX) AS JSON
) AS caseIdentInvs
CROSS APPLY OPENJSON (caseIdentInvs.invs)
WITH (
userId INT,
invTypeCd CHAR(5),
importId INT,
seqId INT
) I;
WITH cte AS
(
SELECT i.*,ci.case_ident_id AS case_ident_id, ki.inv_type_name AS inv_type_name
FROM #tmpInvs i
INNER JOIN case_idents ci ON i.importId=ci.import_id
INNER JOIN kdd_inv_type ki ON i.invTypeCd=ki.inv_type_cd
)
IF EXISTS (SELECT i.seqId FROM #tmpInvs i WHERE i.seqId != NULL)
UPDATE T
SET
inv_id = ct.userId,
inv_type_cd = ct.invTypeCd,
inv_type_name = ct.inv_type_name
FROM case_ident_to_inv T
INNER JOIN cte AS ct ON ct.case_ident_id = T.case_ident_id
WHERE seq_id = ct.seqId
ELSE
INSERT INTO case_ident_to_inv(inv_id, case_ident_id, inv_type_cd, inv_type_name)
SELECT userId, case_ident_id, invTypeCd, inv_type_name
FROM cte
Aucun commentaire:
Enregistrer un commentaire