I've a table for save sub tasks data.

some time we change status of sub tasks, and i need both of them id and status in @JsonStatus parameter.

I wrote a stored procedure like below :

Declare @JsonStatus varchar(1000)       = '[{"id":147},{"id":151,"status2":2}]'
        ,@ID int

--TRUE FORMAT IS : '[{"id":147,"status":3},{"id":151,"status":2}]'
        Update dbo.TaskSub
        Set StatusID = JC.StatusID
        From OpenJson(@JsonStatus) 
        With(   ID                  bigint          'strict $.id',
                StatusID            tinyint         'strict $.status'
            ) as JC                                     
        Where dbo.TaskSub.id = @ID 
            AND dbo.TaskSub.ID = JC.ID

Some time developers not send a parameters but i cant raiserror or etc for them, id and status is mandatory.

I tried use strict statement by read this article if use select its work well like below :

Select *
From OpenJson(@JsonStatus) 
With(   ID                  bigint          'strict $.id',
        StatusID            tinyint         'strict $.status'
    ) as JC 

but not worked in update and result is :

(0 rows affected)

  • Is "status2":2 in the input JSON a typing error? And your SELECT statement using strict modifier (the last statement in the question) generates an error (and this is the expected behaviour with strict modifier, because first array item does not have status key). – Zhorov Jul 30 '20 at 7:47
  • @zhorov I use this update in try catch and use raisereor in try catch, but not work by this type of json. If you use this json by select statement raisereor worked well, but use update result is 0 rows affected I'm confused. – Amirhossein Jul 30 '20 at 9:05
  • Front and back end developers write different source by this sp, some times they are say, one column not change by update, I found fault. They are send wrong json. I need raisereor when json is not true model. It's horibel, one column update and other column not update. All of them need update. – Amirhossein Jul 30 '20 at 9:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.