Передача табличных данных из хранимой процедуры

[lair]

Речь пойдет о методах получения результатов работы процедуры в виде таблиц для последующей работы с ними в SQL.

Мне в таких случаях всегда хочется спросить: а зачем? Нельзя ли перестроить задачу таким образом, чтобы это перестало быть нужным?

Ну и да, почему просто не использовать табличную функцию?

(а в первом методе надо использовать не временные таблицы, а табличные переменные, чтобы не страдать фигней с удалением временных таблиц)

[Ant00]

Мне в таких случаях всегда хочется спросить: а зачем? Нельзя ли перестроить задачу таким образом, чтобы это перестало быть нужным?

Ну и да, почему просто не использовать табличную функцию?

Это не всегда возможно, по крайне мере, в приложениях, где бизнес-логика в SQL. Например, у нас есть два отчета, для которых сбор данных одинаков и он достаточно громоздкий (например ~500 строк кода), а отличаются они только группировкой в конце. В таких случаях и нужно выносить общую часть в отдельную процедуру.

[lair]

Почему нельзя использовать табличную функцию?

[Ant00]

Почему нельзя использовать табличную функцию?

Можно использовать и табличную функцию и конструкцию insert… exec, но только если нам нужно вернуть только одну таблицу в качестве результата. К тому же, kolu4iy:

P.S. Табличные функции — удобно. Но по доброй традиции, в MS SQL есть серьезные ограничения (по сравнению с хранимыми процедурами) на использование функций.

[lair]

Не нужна вам конструкция insert/exec, если ваша задача — одинаково собрать данные, а потом по-разному сгруппировать — просто группируйте сразу результат. А если у вс больше одной таблицы — тогда у вас уже сложная структура данных, и ее надо фиксировать в БД явно, и каждая хранимка пишет и читает туда/оттуда явно.

А про ограничения я бы послушал.

[kolu4iy]

Собственно, все описано в документации, в разделе «совместимость»:

В функциях допустимы следующие инструкции.

• Инструкции присваивания.
• Инструкции управления потоком, за исключением инструкций TRY...CATCH.
• Инструкции DECLARE, объявляющие локальные переменные и локальные курсоры.
• Инструкции SELECT, которые содержат списки выбора с выражениями, присваивающими значения локальным переменным.
• Операции над локальными курсорами, которые объявляются, открываются, закрываются и освобождаются в теле функции.
• Допустимы только те инструкции FETCH, которые предложением INTO присваивают значения локальным переменным. Инструкции FETCH, возвращающие данные клиенту, недопустимы.
• Инструкции INSERT, UPDATE и DELETE, которые изменяют локальные табличные переменные.
• Инструкции EXECUTE, вызывающие расширенные хранимые процедуры.
• Дополнительные сведения см. в разделе Создание определяемых пользователем функций (компонент Database Engine).

Для меня, например, зачастую отсутствие TRY...CATCH, особенно с использованием Linked servers, является определяющим при выборе что именно использовать.

[kolu4iy]

Вариант:

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 есть серьезные ограничения (по сравнению с хранимыми процедурами) на использование функций.

[kolu4iy]

И, кстати, INSERT...EXEC не требует перечисления полей без необходимости:

INSERT INTO @Docs
EXEC Proc1

либо

INSERT INTO #Docs
EXEC Proc1

— вполне легитимные варианты.

[Ant00]

Сейчас покрутил процедуры то так, то эдак — действительно в конструкции insert… exec нет разницы, есть ли перечисление. Однако во всех других случаях, я стараюсь перечислять поля при вставке в таблицу, даже если SQL позволяет этого не делать. Например, кто-то может добавить или поменять в таблице колонки местами и не везде, где она используется, скорректировать. Не раз такое бывало, особенно со * :) Единственное исключение, если данный тип таблицы описан в этом же скрипте/процедуре.

[Ant00]

Спасибо за напоминание про типы, как с 2005 перешли, так их и не использовали. :) Позже поправлю пост.

В табличные переменные нельзя вставить данные внутри процедуры, однако INSERT...EXEC вполне работает. Зачастую достаточно и их. Когда недостаточно — используем следующий трик:

-- Создаем таблицу
SELECT *
INTO #Docs
FROM @Docs

… и вот у нас таблица нужной нам структуры.

Насколько я знаю, нельзя вставлять данные в переменные табличного типа переданные процедуре в качестве параметров, т.к. они readonly.

[kolu4iy]

-- Создаем таблицу
SELECT *
INTO #Docs
FROM @Docs

В данном случае временную таблицу надо создавать перед вызовом процедуры — ибо иначе результатов вы не увидите в силу области ее видимости. Временная таблица (с одной "#") будет видна только в самой процедуре и в тех процедурах, которые она вызывает (аналог PRIVATE почти во всех языках программирования). И, собственно, данный INSERT происходит ВНЕ процедуры.

Если же мы рассматриваем случай с

INSERT INTO @Docs
EXEC Proc1

то здесь табличная переменная вовсе не параметр процедуры, а своя собственная табличная переменная, в которую мы совершаем стандартный INSERT.

Т.е. в обоих случаях, внутри процедуры мы не трогаем данные табличные переменные и/или таблицы, и возвращаем таблицу как в самом первом примере,

create procedure Proc1
as
begin
    select 1 p1, 'b' p2
end

[Ant00]

Понятно, спасибо.

Я подумал, что

-- Создаем таблицу
SELECT *
INTO #Docs
FROM @Docs

Предлагалось разместить внутри вызываемой процедуры :)

[jonie]

Старый (но вроде рабочий, но сильно нешустрый — но малоли кому надо получить из 2000го сервера данные) метод это делать выборку из FROM OPENROWSET(...)…