Передача массива id в хранимую процедуру SQL для удаления

Быстрый ответ

Если вы хотите передать список параметров в хранимую процедуру SQL, лучшим решением будет использование параметров, реализованных в виде таблицы (Table-Valued Parameters, TVPs). Сначала создайте пользовательский тип таблицы, структура которого будет соответствовать вашему списку. Затем вы сможете использовать этот тип в качестве параметра для хранимой процедуры. Добавьте данные в таблицу и вызовите процедуру – и вуаля, ваш список обработан должным образом, используя функциональность SQL!

Другими словами, преобразуйте ваш список в таблицу — SQL оценит это по достоинству!

Пример на SQL Server будет таким:

-- Создаем тип для пользовательской таблицы
CREATE TYPE AwesomeArrayType AS TABLE ( 
    MagicNumber INT 
);

-- Определяем хранимую процедуру для работы со списком
CREATE PROCEDURE AddAwesomesauce
    @TheArrayItself AwesomeArrayType READONLY
AS
    SET NOCOUNT ON;
    INSERT INTO SomePlaceWelcoming (SomePlaceColumn)
    SELECT MagicNumber FROM @TheArrayItself;

-- А теперь самое интересное. Заполняем список данными и выполняем процедуру!
DECLARE @MagicList AwesomeArrayType;
INSERT INTO @MagicList VALUES (10), (42), (66); 
EXEC AddAwesomesauce @MagicList;

Вы создали @TheArrayItself как аналог списка, заполнили его значениями, используя INSERT INTO @MagicList и успешно передали в процедуру с помощью EXEC.

Продвинутые возможности TVP

Работа с XML

Если метод с использованием TVP не подходит, может помочь сериализация XML. Вот как это выглядит:

CREATE PROCEDURE AddMagicsWithXML
    @MagicXML XML
AS
    INSERT INTO SomePlaceWelcoming (SomePlaceColumn)
    SELECT N.value('.', 'INT')
    FROM @MagicXML.nodes('/SomePlace/MagicNumber') as T(N);

Как оказалось, XML может стать универсальным инструментом для перевода списков в SQL-формат.

Подробнее об этом расскажет наш спикер на видео

Dynamic SQL: гибкость перед лицом переменчивости

Когда требуется работа с изменяющимися параметрами, на помощь приходит динамический SQL. Однако стоит быть осторожными и использовать его аккуратно, чтобы избежать SQL-инъекций.

CREATE PROCEDURE AddMagicNumbersDynamic
    @MagicNumbers VARCHAR(MAX)
AS
    EXEC ('INSERT INTO SomePlaceWelcoming (SomePlaceColumn) VALUES ' + @MagicNumbers);

Работа с динамическим SQL требует умелого управления входными данными и внимания к деталям для обеспечения безопасности.

Разделение строк: функционал SQL Server 2016

Если ваш список представлен строкой с разделителями, функционал SQL Server 2016 предусматривает разделение таких строк:

SELECT [value]
FROM STRING_SPLIT(@String, ',');

Разделение строк — это инструмент, который помогает преобразовать строки в списки, потребует от вас дополнительной настройки.

Визуализация

Передачу списка параметров в хранимую процедуру можно представить как отправку автобуса с данными в гостиницу:

Array 🚌: [Data1, Data2, Data3...]

Хранимая процедура принимает все данные, будто это гостиница:

EXEC sp_AcceptGuests @GuestList; -- Разгрузка данных в гостинице данных!

Таким образом, список 🚌 доставляет данные, а хранимая процедура 🏨 их принимает.

Практические примеры

Использование 'NOT IN' при удалении данных

Если вы хотите удалить записи, которых нет в спике, воспользуйтесь конструкцией NOT IN:

DECLARE @Ids AwesomeArrayType;
INSERT INTO @Ids VALUES (1), (3), (4);
DELETE FROM DataTable 
WHERE IdColumn NOT IN (SELECT MagicNumber FROM @Ids);

Используя NOT IN, вы можете осуществить удаление с учетом логики списка.

Транзакции и блокировки

В процессе выполнения крупных операций важно защищать ваши данные, используя транзакции и надежные блокировки:

BEGIN TRANSACTION
TRY
    -- Начинаем обновление или удаление
    COMMIT
CATCH
    ROLLBACK

Транзакции обеспечивают атомарность все операций и гарантируют целостность данных.

Entity Framework: путь .NET разработчиков

Для .NET разработчиков можно воспользоваться Entity Framework, в котором TVP становится абстракцией:

context.Database.ExecuteSqlCommand(
    "EXEC AddAwesomesauce @MagicList", 
    new SqlParameter("@MagicList", magicList)
);

Entity Framework — это инструмент, который станет настоящим помощником для каждого .NET разработчика.

Полезные материалы

1. Table-Valued Parameters – ADO.NET | Microsoft Learn. Описание работы с TVP при передаче списков в хранимую процедуру SQL Server с использованием .NET.
2. Работа с данными JSON в SQL Server – SQL Server | Microsoft Learn. Использование данных JSON в SQL Server, полезно при передаче списков.
3. Проклятья и благословения динамического SQL. Описание особенностей динамического SQL, полезных при работе со списками параметров.
4. Microsoft Corporation. Работа с данными JSON в SQL Server, что может включать структуры списков.
5. Преобразование разделенной строки в таблицу (Функция разделения строк). Метод разделения строки на строки, что может служить способом передачи списка в хранимую процедуру.