Microsoft SQL Server Reporting Services – мощная платформа, в основном используемая для создания, просмотра, управления и подписки на отчеты. Однако она также может использоваться для выполнения задач по манипулированию данными, таких как обновление, удаление и вставка записей. Это может быть особенно полезно в ситуациях, когда отсутствуют опытные программисты или ограничения бюджета и времени.

Ключевым моментом для использования Reporting Services для манипулирования данными является использование хранимых процедур в качестве источников данных. При выполнении хранимой процедуры в SQL Server Reporting Services процедура должна получать значения параметров, выполнять некоторую обработку и возвращать набор данных. Шаг обработки может включать различные операции, включая вставку новых данных, обновление существующих записей или удаление элементов.

Хотя возможно написание логики непосредственно в отчете с использованием набора данных Text, часто более удобно и проще отлаживать при использовании хранимых процедур. Кроме того, если одна и та же логика используется в нескольких отчетах, более эффективно иметь ее централизованно в хранимой процедуре.

При использовании хранимых процедур в Reporting Services важно отметить, что процедура должна возвращать единственный набор результатов. Reporting Services ожидает получить единственный набор результатов для отображаемых данных.

Давайте рассмотрим несколько примеров использования Reporting Services для манипулирования данными:

Обновление строк

Рассмотрим следующую пример хранимой процедуры, которая перемещает всех сотрудников от одного менеджера к другому в базе данных AdventureWorks:

CREATE PROCEDURE MoveEmployeesFromManagerToManager
@SourceManagerID INT,
@DestinationManagerID INT
AS
DECLARE @RCount INT
DECLARE @Output AS TABLE
( Msg NVARCHAR(MAX) )
BEGIN TRY
INSERT INTO @Output
VALUES(
N'Перемещение сотрудников от ManagerID '
+ CONVERT(nvarchar(50), @SourceManagerID)
+ ' к ManagerID '
+ CONVERT(nvarchar(50), @DestinationManagerID)
+ '...'
)
BEGIN TRANSACTION
UPDATE Employees
SET
ManagerID = @DestinationManagerID
OUTPUT
N'Перемещен EmployeeID '
+ CONVERT(nvarchar(50), INSERTED.EmployeeID)
INTO
@Output
FROM
HumanResources.Employee AS Employees
WHERE
ManagerID = @SourceManagerID
SET @RCount = @@ROWCOUNT;
IF @RCount > 0
BEGIN
INSERT INTO @Output
VALUES(
N'Перемещено '
+ CONVERT(nvarchar(50), @RCount)
+ ' сотрудник(ов).')
END
ELSE
BEGIN
INSERT INTO @Output
VALUES(N'Сотрудники не были обновлены.')
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
INSERT INTO @Output
VALUES(N'КРИТИЧЕСКАЯ ОШИБКА: ' + ERROR_MESSAGE())
END CATCH
SELECT * FROM @Output

В этом примере сообщение вставляется в таблицу @Output перед выполнением операции обновления. Синтаксис UPDATE…OUTPUT…INTO используется для сохранения обновленных сотрудников в таблице @Output. Наконец, сообщение, указывающее на успешность или неудачу операции, вставляется в таблицу @Output. Вся логика обернута в блок TRY…CATCH для обработки возможных ошибок.

Удаление строк

Еще один пример включает удаление записи из таблицы. Рассмотрим сценарий, в котором у нас есть два отчета: один отображает содержимое таблицы, а другой удаляет определенную запись из этой таблицы.

Первый отчет содержит кнопку, которая переходит ко второму отчету и передает ID записи для удаления. Второй отчет использует хранимую процедуру для удаления записи и отображает сообщение об операции.

Вот скрипт для хранимой процедуры:

CREATE PROCEDURE DeleteDatabaseLogRow
@DatabaseLogID INT
AS
DECLARE @Output AS TABLE
(Msg NVARCHAR(MAX))
BEGIN TRY
INSERT INTO @Output
VALUES ('Удаление записи с DatabaseLogID ' + CONVERT(varchar(50),@DatabaseLogID))
DELETE FROM DatabaseLog
WHERE DatabaseLogID = @DatabaseLogID
IF @@ROWCOUNT > 0
INSERT INTO @Output
VALUES('Запись успешно удалена.')
ELSE
INSERT INTO @Output
VALUES('Ошибка: Запись не найдена.')
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
INSERT INTO @Output
VALUES(N'КРИТИЧЕСКАЯ ОШИБКА: ' + ERROR_MESSAGE())
END CATCH
SELECT * FROM @Output

В этом примере сообщение вставляется в таблицу @Output перед удалением записи. Хранимая процедура проверяет количество затронутых строк и вставляет соответствующее сообщение в таблицу @Output. Любые возникающие ошибки перехватываются и регистрируются в таблице @Output.

Вставка данных

Reporting Services также может использоваться для вставки данных в таблицу. Параметры отчета могут использоваться для предоставления значений столбцов для одной строки. Однако при работе с группой строк более эффективно использовать инструменты, такие как Microsoft Access, связанные с SQL Server или SSIS для импорта нескольких записей из источников, таких как файлы Excel.

Выполнение внешних команд

Один интересный случай использования – выполнение команд вне SQL Server, таких как запуск отдельных файлов EXE или выполнение команд DOS. Это можно сделать с помощью расширенной хранимой процедуры XP_CMDSHELL (при условии, что она включена и у пользователя есть необходимые разрешения).

Например, вы можете создать отчет, который перемещает файлы или папки в новое место, используя базовые команды CMD, такие как XCOPY, DEL, MOVE или DIR. Результат выполнения этих команд можно сохранить в таблице и отобразить в отчете.

Вот пример использования XP_CMDSHELL в сочетании с таблицей для перемещения целой папки из одного места в другое:

SET @CMD = 'xcopy /E /C "' + @Source + N'\*.*" "' + @Destination + '\*.*"'
INSERT INTO @Msg
EXEC xp_cmdshell @CMD

Как видите, возможности использования Reporting Services для манипулирования данными почти бесконечны. Одн