Комментарии 13
Речь пойдет о методах получения результатов работы процедуры в виде таблиц для последующей работы с ними в SQL.
Мне в таких случаях всегда хочется спросить: а зачем? Нельзя ли перестроить задачу таким образом, чтобы это перестало быть нужным?
Ну и да, почему просто не использовать табличную функцию?
(а в первом методе надо использовать не временные таблицы, а табличные переменные, чтобы не страдать фигней с удалением временных таблиц)
Мне в таких случаях всегда хочется спросить: а зачем? Нельзя ли перестроить задачу таким образом, чтобы это перестало быть нужным?
Ну и да, почему просто не использовать табличную функцию?
Это не всегда возможно, по крайне мере, в приложениях, где бизнес-логика в SQL. Например, у нас есть два отчета, для которых сбор данных одинаков и он достаточно громоздкий (например ~500 строк кода), а отличаются они только группировкой в конце. В таких случаях и нужно выносить общую часть в отдельную процедуру.
Почему нельзя использовать табличную функцию?
Почему нельзя использовать табличную функцию?
Можно использовать и табличную функцию и конструкцию insert… exec, но только если нам нужно вернуть только одну таблицу в качестве результата. К тому же, kolu4iy:
P.S. Табличные функции — удобно. Но по доброй традиции, в MS SQL есть серьезные ограничения (по сравнению с хранимыми процедурами) на использование функций.
Не нужна вам конструкция insert/exec, если ваша задача — одинаково собрать данные, а потом по-разному сгруппировать — просто группируйте сразу результат. А если у вс больше одной таблицы — тогда у вас уже сложная структура данных, и ее надо фиксировать в БД явно, и каждая хранимка пишет и читает туда/оттуда явно.
А про ограничения я бы послушал.
А про ограничения я бы послушал.
Собственно, все описано в документации, в разделе «совместимость»:
В функциях допустимы следующие инструкции.
Для меня, например, зачастую отсутствие TRY...CATCH, особенно с использованием Linked servers, является определяющим при выборе что именно использовать.
В функциях допустимы следующие инструкции.
- Инструкции присваивания.
- Инструкции управления потоком, за исключением инструкций TRY...CATCH.
- Инструкции DECLARE, объявляющие локальные переменные и локальные курсоры.
- Инструкции SELECT, которые содержат списки выбора с выражениями, присваивающими значения локальным переменным.
- Операции над локальными курсорами, которые объявляются, открываются, закрываются и освобождаются в теле функции.
- Допустимы только те инструкции FETCH, которые предложением INTO присваивают значения локальным переменным. Инструкции FETCH, возвращающие данные клиенту, недопустимы.
- Инструкции INSERT, UPDATE и DELETE, которые изменяют локальные табличные переменные.
- Инструкции EXECUTE, вызывающие расширенные хранимые процедуры.
- Дополнительные сведения см. в разделе Создание определяемых пользователем функций (компонент Database Engine).
Для меня, например, зачастую отсутствие TRY...CATCH, особенно с использованием Linked servers, является определяющим при выборе что именно использовать.
Вариант:
… и используем этот тип:
Что получаем? Получаем ОДНО объявление типа, на основе которого мы можем наплодить сколько угодно таблиц (либо табличных переменных — @Docs).
В табличные переменные нельзя вставить данные внутри процедуры, однако INSERT...EXEC вполне работает. Зачастую достаточно и их. Когда недостаточно — используем следующий трик:
… и вот у нас таблица нужной нам структуры.
P.S. Табличные функции — удобно. Но по доброй традиции, в MS SQL есть серьезные ограничения (по сравнению с хранимыми процедурами) на использование функций.
CREATE TYPE [dbo].[t_Docs] AS TABLE(
[NOMDOC] [varchar](10) NOT NULL,
[CFO] [varchar](2) NULL,
[DEP] [varchar](3) NULL,
[VIDDOC] [varchar](5) NULL,
[MEMBER] [varchar](5) NULL,
[MEMBER2] [varchar](5) NULL,
[CALCMEMBER] [varchar](5) NULL,
[SUMMA] [numeric](15, 2) NULL,
[SUMMA0] [numeric](15, 2) NULL,
PRIMARY KEY CLUSTERED
(
[NOMDOC] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO
… и используем этот тип:
DECLARE @Docs AS t_Docs
Что получаем? Получаем ОДНО объявление типа, на основе которого мы можем наплодить сколько угодно таблиц (либо табличных переменных — @Docs).
В табличные переменные нельзя вставить данные внутри процедуры, однако INSERT...EXEC вполне работает. Зачастую достаточно и их. Когда недостаточно — используем следующий трик:
-- Создаем таблицу
SELECT *
INTO #Docs
FROM @Docs
… и вот у нас таблица нужной нам структуры.
P.S. Табличные функции — удобно. Но по доброй традиции, в MS SQL есть серьезные ограничения (по сравнению с хранимыми процедурами) на использование функций.
И, кстати, INSERT...EXEC не требует перечисления полей без необходимости:
либо
— вполне легитимные варианты.
INSERT INTO @Docs
EXEC Proc1
либо
INSERT INTO #Docs
EXEC Proc1
— вполне легитимные варианты.
Сейчас покрутил процедуры то так, то эдак — действительно в конструкции insert… exec нет разницы, есть ли перечисление. Однако во всех других случаях, я стараюсь перечислять поля при вставке в таблицу, даже если SQL позволяет этого не делать. Например, кто-то может добавить или поменять в таблице колонки местами и не везде, где она используется, скорректировать. Не раз такое бывало, особенно со * :) Единственное исключение, если данный тип таблицы описан в этом же скрипте/процедуре.
Спасибо за напоминание про типы, как с 2005 перешли, так их и не использовали. :) Позже поправлю пост.
Насколько я знаю, нельзя вставлять данные в переменные табличного типа переданные процедуре в качестве параметров, т.к. они readonly.
В табличные переменные нельзя вставить данные внутри процедуры, однако INSERT...EXEC вполне работает. Зачастую достаточно и их. Когда недостаточно — используем следующий трик:
-- Создаем таблицу SELECT * INTO #Docs FROM @Docs
… и вот у нас таблица нужной нам структуры.
Насколько я знаю, нельзя вставлять данные в переменные табличного типа переданные процедуре в качестве параметров, т.к. они readonly.
-- Создаем таблицу
SELECT *
INTO #Docs
FROM @Docs
В данном случае временную таблицу надо создавать перед вызовом процедуры — ибо иначе результатов вы не увидите в силу области ее видимости. Временная таблица (с одной "#") будет видна только в самой процедуре и в тех процедурах, которые она вызывает (аналог PRIVATE почти во всех языках программирования). И, собственно, данный INSERT происходит ВНЕ процедуры.
Если же мы рассматриваем случай с
INSERT INTO @Docs
EXEC Proc1
то здесь табличная переменная вовсе не параметр процедуры, а своя собственная табличная переменная, в которую мы совершаем стандартный INSERT.
Т.е. в обоих случаях, внутри процедуры мы не трогаем данные табличные переменные и/или таблицы, и возвращаем таблицу как в самом первом примере,
create procedure Proc1
as
begin
select 1 p1, 'b' p2
end
Старый (но вроде рабочий, но сильно нешустрый — но малоли кому надо получить из 2000го сервера данные) метод это делать выборку из FROM OPENROWSET(...)…
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Передача табличных данных из хранимой процедуры