Поиск
Приведенный ниже запрос я использую ежедневно для поиска упоминаний объектов среди кода хранимых процедур:
SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules AS sm
WHERE sm.definition LIKE '%filter%'
Или, если нам нужно найти код, который находится внутри джоба:
SELECT j.name, step_name
FROM msdb..sysjobs AS j
JOIN msdb..sysjobsteps AS js
ON j.job_id = js.job_id
WHERE command LIKE '%filter%'
Отладка
Часто возникает ситуация, когда необходимо выполнить хранимую процедуру, чтобы проверить корректность ее работы, при этом необходимо, чтобы этот вызов не изменил никаких реальных данных.
В таких случаях можно обернуть вызов процедуры и запросы для проверки данных в одну транзакцию с отменой изменений в конце. Вполне очевидное решение, но не каждый начинающий программист об этом догадывается:
BEGIN TRAN
DECLARE @id INT = 10;
-- Вызов процедуры, которая изменяет реальные данные
EXEC dbo.spDoSomething @param1 = @id;
-- Проверяем, что реальные данные изменились так, как нам нужно
SELECT *
FROM dbo.tblData
WHERE IdData = @id;
-- Возврат состояния системы в исходное состояние
ROLLBACK
Числовые множества и случайные числа
Для генерации множества «на лету», без использования дополнительных таблиц можно использовать рекурсивные CTE:
-- Пример с генерацией чисел от 1 до 100
WITH cte
AS
(
SELECT Number = 1
UNION ALL
SELECT Number = Number + 1
FROM cte
WHERE Number < 100
)
SELECT *
FROM cte
А для генерации случайных чисел или сортировки строк в случайном порядке можно использовать стандартную функцию NEWID():
-- Генерация случайных чисел
SELECT CHECKSUM(NEWID())
-- Генерация случайного числа от 0 до 100
SELECT ABS(CHECKSUM(NEWID())) % 101
-- Сортировка данных в случайном порядке
SELECT *
FROM tblGoods
ORDER BY NEWID()
Передача табличных данных
Иногда перед нами может возникнуть задача, требующая передачи табличных данных из одной процедуры в другую. В случае, когда эти процедуры находятся внутри одной БД, данная задача решается довольно просто. Все, что для этого требуется, это завести новый параметр, возвращающий табличное значение:
-- Создаем собственный тип, возвращающий табличное значение:
CREATE TYPE MyTableValueType
AS TABLE
(
IdField INT NOT NULL UNIQUE,
TextValue VARCHAR(100) DEFAULT('')
);
GO
CREATE PROC dbo.spProcessing
@myTable MyTableValueType READONLY
AS
BEGIN
-- Тут можно использовать нашу табличную переменную @myTable
SELECT *
FROM @myTable
END
Но бывают ситуации, в которых требуется передать табличные данные между серверами, и тогда придется придумывать какой-то другой способ. В случае, когда таковых данных не очень много, я выполняю форматирование данных в виде строки, содержащей данные в формате XML (преобразование в строку нужно т.к. MS SQL не позволяет выполнять удаленный вызов процедур с XML-параметрами):
— Server 1
CREATE PROCEDURE api.spXMLParametrExample
@XML VARCHAR(8000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @xmlTable XML = CAST(@XML AS XML);
SELECT
Id = h.l.value('(./@Id)[1]', 'INT'),
TextValue = h.l.value('(./@TextValue)[1]', 'VARCHAR(50)')
FROM @xmlTable.nodes('./Head/Line') AS h(l)
END
— Server 2
DECLARE @xml VARCHAR(8000);
SET @xml =
(
SELECT
[@Id] = Id,
[@TextValue] = TextValue
FROM tblMyTable
FOR XML PATH('Line'), ROOT('Head')
);
/*
*/
EXEC server1.DB1.api.spXMLParametrExample @XML = @xml;
Агрегация в разрезе строк
Не знаю, как правильно описать то, что я собираюсь продемонстрировать ниже, поэтому приведу пример.
Допустим, у нас есть таблица tblGoods, хранящая информацию о размерах некоторой продукции Size1, Size2, Size3, и нам требуется для каждой строки определить, какое из значений является высотой, иначе говоря, найти наибольшее число среди Size1, Size2, Size3.
Один из вариантов — это использование оператора UNPIVOT, но есть и другой способ, как мне кажется, более наглядный, и в некоторых случаях обладающий большей производительностью (точными данными не обладаю, но в приведенном примере это так, в чем вы можете убедиться самостоятельно).
QA Automation Engineer
QA Automation Engineer
Ренессанс Кредит, Москва, можно удалённо, По итогам собеседования
tproger.ru
Вакансии на tproger.ru
Этот способ заключается в использовании подзапроса и конструкции SELECT * FROM (VALUES…).
Для начала подготовим тестовые данные:
-- Создаем тестовую таблицу, заполненную случайными числами
CREATE TABLE tblGoods
ADD
CodeGoods VARCHAR(10),
Size1 INT DEFAULT(ABS(CHECKSUM(NEWID())) % 50),
Size2 INT DEFAULT(ABS(CHECKSUM(NEWID())) % 50),
Size3 INT DEFAULT(ABS(CHECKSUM(NEWID())) % 50);
-- Добавляем 100 строк товаров
INSERT INTO tblGoods(CodeGoods)
VALUES(LEFT(NEWID(), 10))
GO 100
Пример 1, с использованием оператора UNPIVOT:
-- Вариант с использованием оператора UNPIVOT
SELECT
CodeGoods,
Height = MAX(ValueSize)
FROM tblGoods AS g
UNPIVOT
(ValueSize FOR TypeSize IN(Size1, Size2, Size3)) AS unpvt
GROUP BY CodeGoods;
Пример 2, с подзапросом:
-- Вариант без UNPIVOT
SELECT
CodeGoods,
Height = ( SELECT MAX(Size)
FROM ( VALUES(g.Size1), (g.Size2), (g.Size3) ) AS Sizes(Size))
FROM tblGoods AS g
На этом все. Надеюсь, среди читателей найдутся те, кто открыл для себя что-то новое в этой статье.
