Блог

У меня есть две таблицы Colleges amp; Facilities и у меня есть связь «многие ко многим» между этими двумя таблицами через таблицу соединений.

Таблица колледжей:

    id
   name
   location
   facilities 
  

Таблица объектов:

        name
       id
  

Таблица соединений:

        college_id
       facility_id
  

Данные, поступающие из интерфейса, представляют собой данные json в формате ниже

 {
  "name": "ABC College",
  "location": "ABC Location",
  "facilities" [1,2,3,4,5,6] // Id's of facilities
}
  

Предполагая, что средства уже существуют в базе данных.

Сначала я хочу вставить данные колледжа в таблицу COLLEGE, а после этого я хочу обновить таблицу соединений, как показано ниже, где 1 — идентификатор недавно вставленной строки в таблице COLLEGE

 college id | facility id
   1            1
   1            2
   1            3
   1            4
   .... and so on
  

Как я могу создать хранимую процедуру для того же. Где я могу обновить все записи за один раз или с помощью некоторого цикла любого другого лучшего метода.

Я исследовал следующие методы

1. Цикл While
2. Разделение строки
3. Табличный параметр

Я не могу правильно использовать первые два метода, также я не хочу использовать параметр с табличным значением, потому что база данных может измениться в будущем

Есть ли лучший способ добиться этого с помощью хранимой процедуры, пожалуйста, помогите, я борюсь с этим последние 10 дней. Спасибо

Примечание:

1. Хотите избежать вызова нескольких хранимых процедур из серверного кода
2. Хотите избежать вызова хранимой процедуры в цикле из серверного кода.
3. Не хотите использовать метод временной таблицы

Обновление 1: Json не передается непосредственно в хранимую процедуру, я хочу, чтобы хранимая процедура выглядела примерно так, как показано ниже, если возможно

 create procedure spCreateCollege (
  @name varchar(100),
  @location varchar(100),
  @facilityList varchar(500) // Array or comma seprated string (if possible)
)
  

Если я правильно вас понял, вы можете использовать функции JSON. Предполагая, что ваша строка json задана в качестве параметра @js :

 insert into college_facilities (college_id, facility_id)
select c.id, f.value 
from colleges c
cross join openjson(@js, '$.facilities') as f
where c.name =  json_value(@js, '$.location')
  

Логика состоит в том, чтобы выполнить поиск в таблице колледжа по имени, чтобы получить соответствующее id , и использовать openjson() для отмены идентификации массива идентификаторов объектов.

ХОРОШО .. ключевая логика здесь заключается в

• Вставьте новый колледж
• Получить его идентификатор
• Вставка в таблицу College_facilities

Если вы уверены в своих данных, особенно в том, что facilityList разделен запятыми, вы можете использовать его в динамическом SQL как часть инструкции IN .

Ключевой SQL приведен ниже, с полным подходом в этом db<>скрипте

 INSERT INTO Colleges (name, location, facilityList)
    SELECT @name, @location, @facilityList

SET @college_id = SCOPE_IDENTITY()

SET @CustomSQL = 
N'INSERT INTO College_Facilities (college_id, facility_id)'
 N' SELECT '   LTRIM(STR(@college_id))   ', f.id'
 N' FROM Facilities f'
 N' WHERE f.id IN ('   @facilityList   ')'

EXEC (@CustomSQL)
  

Примечания

• Это демонстрирует решение, но не выполняет очистку данных, проверку наличия данных или что-либо подобное. Вы должны сделать это.
• Не называйте хранимые процедуры sp …, поскольку это используется специальными хранимыми процедурами Microsoft.