Хранимые процедуры SQL¶

Вызов веб-сервисов с помощью SQL¶

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

Для работы веб-сервисов "Первой Формы" необходимо выдать права роли, в которую входит служебный пользователь D10TaskUser.

GRANT EXECUTE ON [sys.sp_OASetProperty] TO {D10TaskUserRole}
GRANT EXECUTE ON [sys.sp_OAMethod] TO {D10TaskUserRole}
GRANT EXECUTE ON [sys.sp_OAGetErrorInfo] TO {D10TaskUserRole}
GRANT EXECUTE ON [sys.sp_OADestroy] TO {D10TaskUserRole}
GRANT EXECUTE ON [sys.sp_OAStop] TO {D10TaskUserRole}
GRANT EXECUTE ON [sys.sp_OACreate] TO {D10TaskUserRole}
GRANT EXECUTE ON [sys.sp_OAGetProperty] TO {D10TaskUserRole}

Также надо выдать права на создание экземпляров объектов OLE-автоматизации в пакетах Transact-SQL.

Процедура

Параметры и краткое описание

tc/_ws/_addAcceptant

Добавление подписчика в текущую запрошенную динамическую подпись.

Параметры:

@TaskSignatureId (int) — номер запрошенной подписи, обязателен,
@InitUserID (int) — ID пользователя который добавил акцептанта, обязателен,
@UserID (int) — ID пользователя, которого добавляют в акцептанты, обязателен ,
@WriteComment(bit) — нужно ли писать комментарий, необязателен,
@SendEmail(bit) — отправлять ли письмо, необязателен.

Пример кода:

DECLARE @id int
select @id = ID from TaskSignatures
where signatureId = 5 and taskId = @taskId and SignatureStateID = 1
EXEC dbo.tc_ws_addAcceptant
        @TaskSignatureID=@id,
        @InitUserID=2,
        @WriteComment=0,
        @SendEmail=0,
        @UserID=123

tc_ws_addComment

Добавление комментария в задачу.

Параметры:

@UserID (int) — ID пользователя, от имени которого генерируется комментарий,
@TaskID (int) — ID задачи, в которой пишут комментарий, обязателен,
@Comment nvarchar(8000) — текст комментария, обязателен.

Пример кода:

declare @Comment varchar(1000)
set @Comment = 'привет, мир!'
exec dbo.tc_ws_addComment @UserID = 123, @TaskID = @taskID, @Comment = @Comment

tc_ws_addNewPerformer

Назначение нового исполнителя задачи.

Параметры:

@UserID (int) — ID пользователя, является исполнителем,
@TaskID (int) --- ID задачи, в которой назначается новый исполнитель,
@SessionUserID (int) — ID пользователя, от имени которого выполняется действие, обязателен.

Пример кода:

EXEC tc_ws_addNewPerformer
     @TaskID = @TaskID,
     @UserID = 123,
     @SessionUserID = 1

tc_ws_AddNewSignatureToTask

Запрос подписи в задачу.

Параметры:

@TaskID (int) — ID задачи, в которой запрашивают подпись, обязателен,
@SignatureID (int) — ID подписи, которую запрашивают, обязателен,
@UserID (int) — ID пользователя, который запрашивает подпись, обязателен,
@IsDynamic (bit) — динамическая ли подпись, необязателен,
@Reason (varchar(max)) — причина запроса подписи, необязателен.

Пример кода:

exec dbo.tc_ws_AddNewSignatureToTask
  @taskid = @taskid,
  @signatureid = 3,
  @userid = 123 ,
  @isDynamic = 1,
  @reason ='нужно ли это делать?'

tc_ws_addSubscriber

Добавление пользователя в подписчики задачи.

Параметры:

@UserID (int) — ID пользователя, которого надо подписать к задаче, обязателен,
@TaskID (int) — ID задачи, к которой надо подписать, обязателен,
@IsSubscribeChildTask (bit) — подписывание к дочерним задачам, необязателен,
@SubscribeType (int) — тип подписки. Значения:

o1 — Добавление вручную

o2 — Акцептант

o3 — Скопирован из родительской

o4 — Консультант\Исполнитель\Заказчик\Автоподписка

o5 — Не определено

o6 — Прочее\Автоматизация

o7 — Участник подписанной группы

@SessionUserId (int) — ID пользователя, от имени которого выполняется действие, обязателен.

Пример кода:

exec dbo.tc_ws_addSubscriber
   @userid =  123,
   @taskid = @taskid,
   @SessionUserId = 1

tc_ws_addSubscriberMailed

Добавление пользователя в подписчики с уведомлением по почте (на данный момент на почту уведомления не отправляются, фактически работает как dbo.tc_ws_addSubscriber).

Параметры:

@UserID (int) — ID пользователя, которого надо подписать к задаче, обязателен,
@TaskID (int) — ID задачи, к которой надо подписать, обязателен,
@IsSubscribeChildTask (bit) — подписывание к дочерним задачам, необязателен,
@ActingUserID --- ID пользователя, который подписывает, обязателен.

Пример кода:

exec dbo.tc_ws_addSubscriberMailed
   @userid =  123,
   @taskid = @taskid,
   @IsSubscribeChildTask = 0,
   @actinguserid = 1

tc_ws_AddUserComment

Отправление комментарий конкретному пользователю.

Параметры:

@TaskID (int) — ID задачи, в которой надо написать комментарий, обязателен,
@CommentRecipients (nvarchar(4000)) — список через точку с запятой логин (на английском) получателей, обязателен,
@UserID (int) — ID пользователя отправителя, обязателен,
@Text (nvarchar(8000)) — текст комментария, обязателен,
@NeedSubscribe (bit) — нужно ли подписываться на задачу, необязателен

tc_ws_AddUserCommentWithSms

Отправление пользователю комментарий через смс (требуется подключение к системе Clickatell).

Параметры:

@TaskID (int) — ID задачи, в которой надо написать комментарий, обязателен,
@CommentRecipients (nvarchar(8000)) — список через точку с запятой логин (на английском) получателей, обязателен,
@UserID (int) — ID пользователя отправителя, обязателен,
@Text (nvarchar(8000)) — текст комментария , обязателен,
@NeedSubscribe (bit) — нужно ли подписываться на задачу, необязателен

tc_ws_changeTaskStep

Совершение перехода в задаче.

Параметры:

@StepID (int) — номер перехода который надо сделать, обязателен,
@Userid (int) — ID пользователя, который делает переход, обязателен,
@Taskid (int) — ID задачи, в которой надо сделать переход, обязателен
@InitiatorUserId (int) — ID пользователя-инициатора перехода,

tc_ws_DeletePerformerFromTask

Удаление исполнителя из задачи.

Параметры:

@Taskid (int) — ID задачи, в которой надо удалить исполнителя, обязателен,
@Userid (int) — ID пользователя, которого удаляют из исполнителей, обязателен,
@SessionUserId (int) — ID пользователя, от имени которого выполняется действие, обязателен.

tc_ws_DeleteRequestedSignature

Удаление запрошенной подписи.

Параметры:

@UserID (int) — ID пользователя, который удаляет запрошенную подпись, обязателен,
@TaskSignatureID (int) — номер запрошенной подписи, обязателен

tc_ws_DeleteTask

Удаление задачи.

Параметры:

@UserID (int) — ID пользователя, который удаляет задачу, обязателен,
@TaskID (int) — ID задачи, которая удаляется, обязателен

tc_ws_LinkOrgUnitWithGroup

Установление связи между орг. единицей и группой пользователей.

Параметры:

@OrgUnitID (int) — ID орг. единицы, обязателен,
@GroupID (int) — ID группы, обязателен

tc_ws_moveTask

Перемещение задачи в категорию.

Параметры:

@UserID (int) — ID пользователя, от имени которого будет сделано перемещение, обязателен,
@TaskID (int) — ID перемещаемой задачи, обязателен,
@SubcatID (int) — ID категории, в которую будет перемещена задача, обязателен

tc_ws_newTask

Создание новой задачи.

Параметры:

@UserID (int) — ID пользователя, который будет являться заказчиком, обязателен,
@Task (nvarchar(8000)) — текст задачи, обязателен,
@OrderedTime (datetime) — срок задачи, обязателен (в зависимости от настройки категории),
@Category (int) — ID категории, в которую будет поставлена задача, обязателен,
@Comment (nvarchar(8000)) — первый комментарий к задаче, не обязателен,
@ExtParamStr (nvarchar(8000)) — служебная строка для заполнения доп. параметров, описание см. ниже, обязателен (в зависимости от настройки обязательных ДП при постановке в категории),
@PerfID (int) — ID исполнителя при постановке, обязателен (в зависимости от настройки категории),
@PriorityID (int) — ID приоритета задачи, не обязателен,
@D3TaskID — не используется,
@ParentID (int) — ID родительской задачи, не обязателен,
@UserNick (nvarchar(20))- ник пользователя, ставящего задачу, не обязателен (может использоваться вместо @UserID),
@Remind (bit) — напоминание о приближении срока задачи, не обязателен,
@PropagateSubscribers (bit) — подписать подписчиков родительской задачи, не обязателен,
@PropagateFiles (boolean) — перенести файлы в дочернюю задачу,
@TaskStartTime (datetime) — постановка даты начала работ, не обязателен

ℹ️ ID созданной задачи можно получить так:

EXECUTE @newtaskid = [dbo].[tc_ws_NewTask]

tc_ws_PeriodicExecution

Явное выполнение периодических заданий из очереди

tc_ws_updateExtParamsInTasks

Обновление дополнительных параметров в задаче.

Параметры:

@TaskID (int) — id задачи, обязателен,
@Str (nvarchar(8000)) — служебная строка для заполнения доп. параметров, описание см. ниже, обязателен,
@SessionUserID (int) — id пользователя, от имени которого выполняется действие, обязателен,
@Comment (bit) — если равен 1, то пишется системный комментарий об изменении ДП, если 0 — не пишется; по умолчанию равен 1; не обязателен

tc_ws_updateOrderedTime

Обновление срока задачи.

Параметры:

@TaskID (int) — ID задачи, обязателен,
@OrderedTime (datetime) — срок задачи, обязателен

tc_ws_updateTaskDescription

Обновление текста задачи.

Параметры:

@TaskID (int) — ID задачи, обязателен,
@Description (nvarchar(max)) — новый текст, обязателен

Служебная строка¶

@ExtParamStr или @Str

Заполняет/изменяет значение ДП.

Например, для одного ДП строка имеет вид:

'#nномер#vзначение::'

где номер — ID ДП, а значение — значение, которое должно быть записано в ДП.

Для двух ДП (и более — аналогично):

'#nномер1#vзначение1::#nномер2#vзначение2::'

ℹ️ Все значения должны быть приведены к типу varchar

Пример кода:

Пусть в задаче 15678 от имени пользователя "Диспетчер задач" нужно обновить ДП с ID=167 (тип "Текст"), записать в него значение 'Иван Иванович', а в ДП с ID=168 (тип "ДатаВремя") записать текущую дату.

Строку можно задекларировать следующим образом:

set @str = '#n167#vИван Иванович::#n168#v'+ cast (getdate() as varchar) + '::'

ID пользователя "Диспетчер задач" получим следующим образом:

select @UserID = SystemRobotID from Settings where CustomerID = 1

Наконец, сделаем вызов процедуры:

exec tc_ws_UpdateExtParamsInTasks
   @TaskID = 15678,
   @Str = @str,
   @SessionUserID = @userid

ℹ️ Обратите внимание:

чтобы сохранить символы '::' внутри значения ДП -- поставьте перед ними обратный слеш '\::'
чтобы поместить слеш в конце значения ДП -- продублируйте его '\::'

Пример работы с таблицей:

Пусть в ДП "Таблица" с ID=999 колонки 11 и 33 заполняются, а колонка 22 остается пустой.

declare @str as varchar (1000) = '+[{11:{"First":"Текст1111"},22:{"First":""},33:{"First":"Текст3333"}}]'
set @str = '#n999#v' + @str

Вызов опубликованного смарт-пакета¶

Если вы работаете с системой на уровне БД, меняяя данные в таблицах напрямую через SQL-запросы, периодически вы можете сталкиваться с тем, что внесенные в БД изменения применяются не сразу. Чтобы обойти это, можно использовать механизм публикаций.

Чтобы сделать http-запрос к опубликованному смарт-пакету, можно использовать [следующий скрипт](../.

Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
Exec sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT;
Exec sp_OAMethod @Object,
  'open',
  NULL,
  'get',
  'ссылка',
  'false'
Exec sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
Select  @ResponseText
Exec sp_OADestroy @Object

где ссылка — это Url для обращения к опубликованному смарт-пакету, с возможностью задать входящие параметры. Общий вид ссылки:

https://адрес_приложения/app/v1.2/api/publications/action/название_пакета?param1=value1&param2=value2...

Вызов задания¶

Некоторые хранимые процедуры нужно вызывать регулярно по расписанию. Часто для этого используются смарт-расписания. Но если процедура обрабатывает большие объемы данных (например, при интеграции с внешними системами), то смарт-расписание будет прерываться по тайм-ауту. Чтобы избежать этого, такие хранимые процедуры можно вызывать как задания (джобы).

Пример:

exec msdb.dbo.sp_start_job 'название_хранимой_процедуры'

Получение входящих параметров¶

Часто набор входящих параметров передается в формате XML (например, при использовании фильтров в иерархиях, виджетах, планировщиках и т.п.). Для обработки таких входящих параметров на MS SQL удобно использовать процедуру crm_repGetParams, поставляемую вместе с "Первой Формой".

Имя параметра передается как атрибут Name. Для периода времени передаются две даты — From и To, для остальных параметров передается значение Value.

Получение значений входящих параметров из формата XML с помощью процедуры crm_repGetParams может выглядеть следующим образом:

ALTER PROCEDURE [...]
(
 @XmlParam xml = null,
 @DrillDownField varchar(max) = null,
 @DrillDownParams varchar(max) = null,
 @UserID int = null
)

AS
BEGIN
 -- объявление параметров и вспомогательной таблицы
 declare @StartDate date = null, @EndDate date = null, @Filial int = null
 declare @Params table ([Name] varchar(max), [From] varchar(max), [To] varchar(max), [Value] varchar(max))

 -- запись значений параметров во вспомогательную таблицу
 insert into @Params ([Name], [From], [To], [Value])
               exec crm_repGetParams @XML = @XmlParam

 -- получение значений параметров
 select @StartDate = cast([From] as varchar(max)), @EndDate = cast([To] as varchar(max))
                 from @Params where Name = 'Period'

 select @Filial = cast([Value] as varchar(max))
         from @Params where Name = 'Filial'
 ...
END

Для PG процедура crm_repGetParams не используется. Пример передачи параметра для PG:

DECLARE
d1 DATE := NULL;
d2 DATE := NULL;
BEGIN
SELECT a.from_val, a.to_val into d1, d2 FROM dbo.ssl_get_filter_param_by_name(xmlparam::xml, 'period') as a;

Проверка прав доступа¶

Для проверки прав доступа пользователей на задачи рекомендуется использовать стандартные системные табличные функции fn_UserTaskPermissions и fn_CheckUserTaskPermissions.

Использование функции dbo.fn_CheckUserTaskPermissions¶

[Пример](../ вывода списка задач, которые доступны пользователю из определенной или любой категории.

declare @UserID int = 2950, @SubcatID int = 128;

-- доступные пользователю задачи из указанной категории
select t.TaskID from dbo.Tasks t with (nolock)
cross apply dbo.fn_CheckUserTaskPermissions (t.TaskID, @UserID, @SubcatID) chtp
where t.SubcatID = 128

-- доступные пользователю задачи из любой категории
select top 1000 t.TaskID from dbo.Tasks t with (nolock)
cross apply dbo.fn_CheckUserTaskPermissions (t.TaskID, @UserID, t.SubcatID) chtp
where t.IsClosed = 0

Использование функции dbo.fn_UserTaskPermissions()

[Пример](../ быстрого вывода списка задач, которые доступны пользователю из определенной категории.

select TaskID from dbo.fn_UserTaskPermissions (@UserID, @SubcatID)

Вместо объединения UserTaskPermissions и таблицы Tasks эффективнее использовать функцию проверки прав пользователя dbo.fn_UserTaskPermissions(). Она имеет два аргумента: @UserID (ID пользователя) и @SubcatID (ID категории). Функция возвращает все записи указанной категории, доступные указанному пользователю, и содержит все поля, которые есть в таблице Tasks.

Пример:

select  *
from   dbo.fn_UserTaskPermissions(@UserID, @SubcatID) utp
where   utp.StateId in (1, 2) and
      utp.PlannedEnd < getdate()

Объединение результата dbo.fn_UserTaskPermissions() с Tasks

Поскольку все поля можно брать из результата функции, в большинстве случаев нет необходимости дополнительно связывать в запросе результат dbo.fn_UserTaskPermissions() с таблицей Tasks — это создает лишнюю нагрузку на процессор.

Если же в вашем запросе невозможно обойтись без связи с таблицей Tasks, а простой join с dbo.fn_UserTaskPermissions() начинает заметно тормозить даже на небольшой выборке, попробуйте следующие варианты:

1. Вместо прямого join с dbo.ваша_таблица по TaskID используйте [конструкцию](../.

...
from   dbo.ваша_таблица
    cross apply
    (
        select top 1 *
        from   dbo.fn_UserTaskPermissions(@UserID, @SubcatID) utp
        where   utp.TaskID = dbo.ваша_таблица.TaskID
    ) utp
...

Это заставит сервер применить проверку прав после отбора по dbo.ваша_таблица.

2. Добавьте в самый конец оператора option (force order).

Использование dbo.fn_UserTaskPermissions() для нескольких пользователей и/или категорий¶

Если вам нужно получить задачи, доступные N пользователям (например, с учетом заместителей), и/или задачи из M категорий, функцию dbo.fn_UserTaskPermissions() необходимо вызывать N*M раз.

Пример 3:

Два пользователя с ID 111 и 222, две категории с ID 333 и 444.

select  *
from    (
          select * from dbo.fn_UserTaskPermissions(111, 333)
          union
          select * from dbo.fn_UserTaskPermissions(111, 444)
          union
          select * from dbo.fn_UserTaskPermissions(222, 333)
          union
          select * from dbo.fn_UserTaskPermissions(222, 444)
       ) utp
where   utp.StateId in (1, 2) and
      utp.PlannedEnd < getdate()

В данном примере используется union (а не union all) для исключения дублирующихся записей.

Пример 4 (применим если не используются поля из таблицы Tasks):

Два пользователя с ID 111 и 222, две категории с ID 333 и 444.

select distinct TagID
from   dbo.TaskTags tt
where   tt.TaskID in
       (
          select utp.TaskID from dbo.fn_UserTaskPermissions(111, 333) utp
          union all
          select utp.TaskID from dbo.fn_UserTaskPermissions(111, 444) utp
          union all
          select utp.TaskID from dbo.fn_UserTaskPermissions(222, 333) utp
          union all
          select utp.TaskID from dbo.fn_UserTaskPermissions(222, 444) utp
       )

В данном примере предпочтительнее использовать union all (а не union) из-за особенностей оптимизатора.

Использование dbo.fn_UserTaskPermissions() для неопределённого количества категорий¶

Если количество категорий заранее не известно, тогда применение функции dbo.fn_UserTaskPermissions() становится не настолько эффективным, но все же возможным. В этом случае рекомендуется провести эксперимент: в качестве второго параметра передать null и проанализировать снижение производительности. Если снижение существенно, стоит отказаться от использования функции dbo.fn_UserTaskPermissions() и использовать join с представлением UserTaskPermissions.

Доступные настройки¶

События, на которые можно назначить выполнение хранимых процедур в интерфейсе системы:

При постановке задачи: Категории и процессы — Категории — Настройки — опция Процедура при постановке (SQL) (Устаревшее, не используется)
При сохранении задачи: Категории и процессы — Категории — Настройки — опция Процедура при сохранении (Устаревшее, не используется)
При переходе по маршруту: Категории и процессы — Категории — Маршрут — Настройки перехода — Настройки — опция Процедура при переходе (SQL)
При подписании на переходе: Категории и процессы — Категории — Маршрут — Настройки перехода — Подписи на переходе — опция Процедура
При завершении обработки писем: Категории и процессы — Категории — Почта — Настройки перехода — Настройки почтового ящика — опция Процедура при завершении обработки (SQL)
При изменении статуса: Категории и процессы — Статусы — Настройки статуса — опция Выполняется встроенная процедура

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

Диагностика работы СУБД¶

С помощью процедуры sp_Blitz можно проанализировать производительность работы СУБД и выявить "узкие места".

Каждый вызов настраивает результат проверки через параметр @OutputType или фильтрует выводимые данные по уровню важности с помощью параметров @IgnorePrioritiesBelow и @IgnorePrioritiesAbove.

Примеры вызовов:

EXEC sp_Blitz;

Запускает полную диагностику и выводит список всех найденных проблем, предупреждений и рекомендаций в виде таблицы.

EXEC sp_Blitz @IgnorePrioritiesAbove = 50;

Показывает только критические проблемы с высоким приоритетом (до 50 включительно), скрывая менее важные сообщения.

EXEC sp_Blitz @IgnorePrioritiesBelow = 200;

Фильтрует вывод, оставляя только информационные сообщения и рекомендации с приоритетом от 200 и выше.

EXEC sp_Blitz @IgnorePrioritiesBelow = 50, @IgnorePrioritiesAbove = 150;

Выводит проблемы в определённом диапазоне приоритетов (от 50 до 150), исключая как очень критические, так и чисто информационные записи.

EXEC sp_Blitz @OutputType = 'COUNT';

Не выводит детальный список, а возвращает только общее количество найденных проблем.

EXEC sp_Blitz @OutputType = 'XML';

Формирует результат проверки в структурированном XML-формате.

EXEC sp_Blitz @OutputType = 'MARKDOWN';

Генерирует отчет в формате Markdown.

EXEC sp_Blitz @OutputType = 'CSV';

Возвращает данные в формате CSV (значения, разделённые запятыми).

EXEC sp_Blitz @OutputType = 'SCHEMA';

Возвращает не сами данные диагностики, а метаинформацию: схему (структуру) выходной таблицы с именами и типами столбцов.

Полезные ссылки¶

Администрирование хранимых процедур

Структура БД

Обращение к различным типам ДП в SQL запросах

Описание отдельных таблиц и значений

Хранимые процедуры SQL¶

Рекомендации¶

Вызов веб-сервисов с помощью SQL¶

Служебная строка¶

Вызов опубликованного смарт-пакета¶

Вызов задания¶

Получение входящих параметров¶

Проверка прав доступа¶

Использование функции dbo.fn_CheckUserTaskPermissions¶

Использование dbo.fn_UserTaskPermissions() для нескольких пользователей и/или категорий¶

Использование dbo.fn_UserTaskPermissions() для неопределённого количества категорий¶

Доступные настройки¶

Диагностика работы СУБД¶

Полезные ссылки¶