#sql-server #database #stored-procedures #insert #many-to-many
#sql-сервер #База данных #хранимые процедуры #вставить #многие ко многим
Вопрос:
У меня есть две таблицы 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
Как я могу создать хранимую процедуру для того же. Где я могу обновить все записи за один раз или с помощью некоторого цикла любого другого лучшего метода.
Я исследовал следующие методы
- Цикл While
- Разделение строки
- Табличный параметр
Я не могу правильно использовать первые два метода, также я не хочу использовать параметр с табличным значением, потому что база данных может измениться в будущем
Есть ли лучший способ добиться этого с помощью хранимой процедуры, пожалуйста, помогите, я борюсь с этим последние 10 дней. Спасибо
Примечание:
- Хотите избежать вызова нескольких хранимых процедур из серверного кода
- Хотите избежать вызова хранимой процедуры в цикле из серверного кода.
- Не хотите использовать метод временной таблицы
Обновление 1: Json не передается непосредственно в хранимую процедуру, я хочу, чтобы хранимая процедура выглядела примерно так, как показано ниже, если возможно
create procedure spCreateCollege (
@name varchar(100),
@location varchar(100),
@facilityList varchar(500) // Array or comma seprated string (if possible)
)
Ответ №1:
Если я правильно вас понял, вы можете использовать функции 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()
для отмены идентификации массива идентификаторов объектов.
Комментарии:
1. Привет, я только что обновил вопрос, мы не передаем json непосредственно в хранимую процедуру. Спасибо за это, я попробую напрямую передать json 🙂
Ответ №2:
ХОРОШО .. ключевая логика здесь заключается в
- Вставьте новый колледж
- Получить его идентификатор
- Вставка в таблицу 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.