При проектировании кода базы данных для большого приложения важно сделать его переиспользуемым и управляемым. Это повышает управляемость кода, обеспечивает большую переиспользуемость и в конечном итоге приводит к лучшей производительности и меньшему количеству ошибок. Однако иногда мы сталкиваемся с ограничениями в TSQL, языке, используемом в SQL Server, которые ограничивают нашу свободу в проектировании переиспользуемого кода.
В этой статье мы рассмотрим сценарий, когда нам нужно обновить запас товаров, затронутых транзакцией, в системе управления запасами. У нас уже есть хранимые процедуры для сохранения/обновления каждой транзакции, но каждая из этих хранимых процедур должна обновить запас всех товаров, затронутых транзакцией. Чтобы сделать код более управляемым и переиспользуемым, было бы идеально переместить эту часть кода в отдельную хранимую процедуру, которую можно вызывать из разных мест.
Однако возникает проблема, когда нам нужно передать товары для обновления в хранимую процедуру. SQL Server не позволяет нам передавать переменную TABLE в качестве параметра хранимой процедуры. Так какова следующая опция?
В этой статье мы представим решение, используя XML в качестве формата для передачи таблицы в хранимую процедуру. Вызывающая сторона может преобразовать таблицу (результат запроса) в переменную XML и передать ее в хранимую процедуру. Получатель может либо преобразовать XML-параметр обратно в переменную TABLE, либо непосредственно использовать XQuery с переменной XML.
Вызывающая сторона
Вызывающая сторона должна преобразовать таблицу в переменную XML. В следующем примере показано, как создать переменную XML из результатов запроса:
/* Давайте сначала создадим пример таблицы. */
CREATE TABLE [dbo].[OrderDetails](
[OrderDetailID] [int] IDENTITY (1,1) NOT NULL ,
[ItemNumber] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qty] [int] NULL
) ON [PRIMARY]
/* Заполним пример таблицы значениями */
INSERT INTO OrderDetails(ItemNumber, Qty)
SELECT 'A001', 10
UNION SELECT 'A002', 20
UNION SELECT 'A003', 30
/* Запрос ниже возвращает результаты в формате XML. */
SELECT ItemNumber, Qty FROM OrderDetails FOR XML RAW ('item'), ROOT ('items')
На этом этапе у нас есть готовая переменная XML, которую мы можем передать дочерней процедуре или функции. Переменная XML содержит значения, которые мы хотим, чтобы дочерняя процедура или функция обработала или обновила.
Получатель
Получатель – это хранимая процедура или функция, которая получает XML-параметр и обновляет данные о запасах на основе переданной информации о товаре. Один из способов достичь этого – создать обертку вокруг переменной XML и использовать ее, как если бы это была таблица. Другой подход – обновить таблицу напрямую из переменной XML.
Вот пример реализации, необходимой на стороне получателя:
CREATE PROCEDURE [dbo].[UpdateInventory]
(
@x XML
)
AS
SET NOCOUNT ON
/* Версия этой хранимой процедуры обновляет таблицу напрямую из переменной XML. */
UPDATE Inventory SET
stock = stock + x.item.value('@Qty[1]', 'INT')
FROM Inventory inv
INNER JOIN @x.nodes('//items/item') x(item) ON
(x.item.value('@ItemNumber[1]', 'varchar(20)') = inv.ItemNumber)
Используя XML в качестве формата для передачи таблицы в хранимую процедуру, мы можем преодолеть ограничение, которое не позволяет передавать переменную TABLE напрямую. Этот подход позволяет нам проектировать переиспользуемый и управляемый код, повышая производительность и уменьшая количество ошибок.
Важно отметить, что при использовании этого подхода может быть небольшое снижение производительности. Однако в большинстве случаев влияние на производительность незначительно и не вызывает заметных проблем.
В заключение, используя XML в качестве средства передачи таблицы в хранимую процедуру в SQL Server, мы можем проектировать более переиспользуемый и управляемый код. Этот подход обеспечивает большую гибкость и позволяет повысить производительность в больших приложениях.