При работе с SQL Server часто возникают ситуации, когда необходимо извлечь данные из таблицы или представления с одним или несколькими параметрами. Однако возникает проблема, когда значение параметра изменяется в зависимости от различных факторов. В таких случаях можно использовать хранимую процедуру или функцию с возвращаемой таблицей (TVF), которая принимает параметр. Но что, если вы не хотите использовать хранимую процедуру или TVF?
Один из вариантов – использовать представление. Многие разработчики регулярно используют представления, не понимая, что они являются просто ограниченным типом функции или хранимой процедуры, которая может принимать необязательный параметр. Представления могут быть лучшим вариантом, когда вам нужно только добавить некоторые компоненты программирования в запрос, такие как оператор DO WHILE или оператор DECLARE. Однако недостатком использования представления является то, что вы не можете передавать параметры в ваш запрос или использовать значения переменных в представлении.
Для иллюстрации рассмотрим пример таблицы с именем “MyTables” с колонками “Period”, “OtherInfo” и “MoreInfo”. Если у вас есть простое представление вида “SELECT * FROM MyTables WHERE Period = 2022”, оно вернет 5-ю строку данных.
Чтобы преобразовать этот простой запрос в хранимую процедуру, TVF или представление, вы можете выполнить одну из следующих команд:
-- Хранимая процедура
CREATE PROCEDURE proc_TableByPeriod
AS
SELECT * FROM MyTables WHERE Period = 2022
-- Функция с возвращаемой таблицей
CREATE FUNCTION fn_TableByPeriod()
RETURNS TABLE
AS
RETURN (SELECT * FROM MyTables WHERE Period = 2022)
-- Представление
CREATE VIEW v_TableByPeriod
AS
SELECT * FROM MyTables WHERE Period = 2022
Чтобы извлечь данные из хранимой процедуры, вы можете использовать следующую команду:
EXEC dbo.proc_TableByPeriodЧтобы извлечь данные из функции с возвращаемой таблицей, вы можете использовать следующую команду:
SELECT * FROM fn_TableByPeriod()И чтобы извлечь данные из представления, вы просто можете выполнить следующее:
SELECT * FROM v_TableByPeriodПланы выполнения для этих трех методов идентичны, без выигрыша или потери производительности. Однако возникает проблема, когда пользователь хочет найти информацию для альтернативного периода, используя переменный параметр. С функцией с возвращаемой таблицей вы можете передать параметр в оператор SELECT, например, так:
ALTER FUNCTION fn_TableByPeriod(@Period INT)
AS
RETURN
(SELECT * FROM MyTables WHERE Period = @Period)Это позволяет выполнить функцию с определенным периодом, например:
SELECT * FROM fn_TableByPeriod(2022)Аналогично, вы можете отредактировать хранимую процедуру таким же образом и выполнить ее с параметром:
EXEC dbo.proc_TableByPeriod 2022Однако такой подход требует от пользователей и систем изучения другого способа мышления и может быть не подходящим для сред, которые не поддерживают хранимые процедуры или TVF.
Чтобы преодолеть эту проблему, вы можете создать таблицу с фильтрованными параметрами, которая хранит значения переменных. Отслеживая наиболее распространенные значения фильтрации и храня их в таблице, вы можете динамически изменять параметры в вашем представлении. Например, вы можете изменить представление следующим образом:
ALTER FUNCTION fn_TableByPeriod(@Period INT)
AS
RETURN
(SELECT * FROM MyTables WHERE Period = (SELECT TOP 1 Period FROM dw.FilterPeriod))Теперь ваше представление определяется динамически изменяющимися параметрами, и вы можете предоставить интерфейс для изменения значения параметра пользователями. Вы можете применить тот же подход к другим столбцам, таким как “OtherInfo”, используя таблицу с фильтрованными параметрами во всех ваших приложениях.
Этот метод имеет идентичную производительность хранимым процедурам или TVF, но более надежен и удобен для приложений, которые полагаются на простые операторы SELECT. Обновление таблицы параметров можно выполнить различными способами, что выходит за рамки данной статьи. Основная цель здесь – продемонстрировать, как передавать параметры в запросе устойчивым способом.
Понимая различные доступные варианты и используя возможности представлений, хранимых процедур и функций с возвращаемыми таблицами, вы можете эффективно извлекать данные из таблиц и представлений SQL Server с различными параметрами.