DECLARE @json varchar(max) =
N'{
"schoolIds": [1, 2, 3]
}'

DECLARE @studentList TABLE (id bigint)

set transaction isolation level repeatable read
begin tran 
insert into @studentList(id)
select stg_id from stg_student stg 
join openjson(@json,'$.schoolIds') sc on stg.schoolId=value  and ISNULL(status,'') <> 'S' 

update [dbo].[stg_student] set status = 'S' 
from [dbo].[stg_student] s
join @studentList sl
on s.stg_id = sl.id

commit tran
set transaction isolation level read committed

begin tran 
insert into @studentList(id)
select stg_id from stg_student stg 
join openjson(@json,'$.schoolIds') sc on stg.schoolId=value  and ISNULL(status,'') <> 'S' 

declare @res nvarchar(255), @result int

declare res cursor local fast_forward for
 select N'school' + cast(id as nvarchar(10)) from @studentList

open res
while 1 = 1
 begin
  fetch next from res into @res
  if @@fetch_status <> 0
   break;

  exec @result = sp_getapplock @res, N'Exclusive', N'Transaction', -1
  if @result <> 0 -- ошибка
   begin
    ...
   end
 end
close res
deallocate res

update [dbo].[stg_student] set status = 'S' 
from [dbo].[stg_student] s
join @studentList sl
on s.stg_id = sl.id

commit tran

begin tran

insert into @studentList(id)
select
 stg_id
from
 openjson(@json,'$.schoolIds') scl join
 [Справочник школ] sc with (updlock, readpast) on sc.schoolId = a.value join
 stg_student stg on stg.schoolId = sc.schoolId and ISNULL(stg.status,'') <> 'S' 

update [dbo].[stg_student] set status = 'S' 
from [dbo].[stg_student] s
join @studentList sl
on s.stg_id = sl.id

commit tran

DECLARE @json varchar(max) =
N'{
"schoolIds": [1, 2]
}'


DECLARE @studentList TABLE (id bigint)

set transaction isolation level repeatable read
begin tran 

update stg 
set stg.status = 'S' 
output inserted.stg_id into @studentList(id)
from stg_student stg 
join openjson(@json,'$.schoolIds') sc on stg.schoolId=value  and ISNULL(transferred_status,'') <> 'S'

waitfor delay '00:00:15'

commit tran
set transaction isolation level read committed


select ss.schoolId, sl.id from @studentList sl
join stg_student ss
on sl.id = ss.stg_id

DECLARE @json varchar(max) =
N'{
"schoolIds": [2, 3]
}'
select ISJSON(@json)

DECLARE @studentList TABLE (id bigint)

set transaction isolation level repeatable read
begin tran 

update stg 
set stg.status = 'S' 
output inserted.stg_id into @studentList(id)
from stg_student stg 
join openjson(@json,'$.schoolIds') sc on stg.schoolId=value  and ISNULL(transferred_status,'') <> 'S'

waitfor delay '00:00:15'

commit tran
set transaction isolation level read committed


select ss.schoolId, sl.id from @studentList sl
join stg_student ss
on sl.id = ss.stg_id