Доступ к наборам результатов из хранимых процедур Transact-SQL SQL Server

Я использую SQL Server 2005, и я хотел бы знать, как получить доступ к различным наборам результатов из transact-sql. Следующая хранимая процедура возвращает два набора результатов, как мне получить к ним доступ, например, из другой хранимой процедуры?

CREATE PROCEDURE getOrder (@orderId as numeric) AS
BEGIN   
    select order_address, order_number from order_table where order_id = @orderId
    select item, number_of_items, cost from order_line where order_id = @orderId
END

Мне нужно иметь возможность итерации через оба набора результатов в отдельности.

EDIT: просто для выяснения вопроса, я хочу протестировать хранимые процедуры. У меня есть набор хранимых процедур, которые используются с клиентом VB.NET, которые возвращают несколько наборов результатов. Это не будет изменено на функцию с табличной оценкой, я вообще не могу изменить процедуры. Изменение процедуры не является вариантом.

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

Ответ 1

Короткий ответ: вы не можете этого сделать.

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

Чтобы быть полным, если процедура возвращала один результат, вы можете вставить ее в переменную temp или табличную переменную со следующим синтаксисом:

INSERT INTO #Table (...columns...)
EXEC MySproc ...parameters...

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

Ответ 2

Я легко мог это сделать, создав хранимую процедуру CLR SQL2005, содержащую внутренний набор данных.

Вы видите, новый SqlDataAdapter будет. По умолчанию набор данных с множественным результатом sproc в набор данных с несколькими таблицами по умолчанию. Данные в этих таблицах, в свою очередь, могут быть вставлены в таблицы #Temp в вызывающем sproc, который вы хотите записать. dataset.ReadXmlSchema покажет вам схему каждого набора результатов.

Шаг 1: Начните писать sproc, который будет считывать данные из множества результатов sproc

а. Создайте отдельную таблицу для каждого набора результатов в соответствии с схемой.

CREATE PROCEDURE [dbo].[usp_SF_Read] AS
SET NOCOUNT ON;
CREATE TABLE #Table01 (Document_ID VARCHAR(100)
  , Document_status_definition_uid INT
  , Document_status_Code VARCHAR(100) 
  , Attachment_count INT
  , PRIMARY KEY (Document_ID));

б. На этом этапе вам может потребоваться объявить курсор для повторного вызова созданного здесь CLR sproc:

Шаг 2: Создайте CLR Sproc

Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub usp_SF_ReadSFIntoTables()

    End Sub
End Class

а. Подключитесь с помощью New SqlConnection("context connection=true").

б. Настройте объект команды (cmd), чтобы содержать sproc с множественным результатом.

с. Получите все данные, используя следующее:

    Dim dataset As DataSet = New DataSet
    With New SqlDataAdapter(cmd)
        .Fill(dataset) ' get all the data.
    End With
'you can use dataset.ReadXmlSchema at this point...

д. Перейдите по каждой таблице и вставьте каждую строку в соответствующую временную таблицу (которую вы создали на первом шаге выше).

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

Чтобы все это было!

~ Шон, недалеко от Сиэтла

Ответ 3

Существует и то, что вы можете сделать. Добавьте необязательный параметр N int в ваш sproc. По умолчанию значение N равно -1. Если значение N равно -1, сделайте все, что вы выбираете. В противном случае сделайте N-й выбор и выберите только Nth.

Например,

if (N = -1 or N = 0)
    select ...

if (N = -1 or N = 1)
    select ...

Абоненты вашего sproc, которые не указывают N, получат набор результатов с более чем одной таблицей. Если вам нужно извлечь одну или несколько из этих таблиц из другого sproc, просто назовите ваш sproc, указав значение для N. Вам нужно будет вызвать sproc один раз для каждой таблицы, которую вы хотите извлечь. Неэффективен, если вам нужно несколько таблиц из набора результатов, но оно работает в чистом TSQL.

Ответ 4

Обратите внимание, что существует дополнительное недокументированное ограничение для инструкции INSERT INTO... EXEC: она не может быть вложенной. То есть хранимый proc, который вызовет EXEC (или любой, который он вызывает по очереди), сам не может выполнить INSERT INTO... EXEC. Кажется, что на каждый процесс, который накапливает результат, есть один блокнот, и если они вложены, вы получите сообщение об ошибке, когда вызывающий абонент откроет его, а затем вызывающий пытается снова открыть его.

Matthieu, вам нужно будет поддерживать отдельные временные таблицы для каждого "типа" результата. Кроме того, если вы выполняете одно и то же несколько раз, вам может потребоваться добавить дополнительный столбец к этому результату, чтобы указать, из какого вызова он возник.

Ответ 5

К сожалению, это невозможно сделать. Проблема, конечно, в том, что для этого не существует синтаксиса SQL. Это, конечно, происходит "под капотом", но вы не можете получить эти другие результаты в TSQL, только из приложения через ODBC или что-то еще.

Есть способ обойти его, как и большинство вещей. Хитрость заключается в использовании автоматизации ole в TSQL для создания объекта ADODB, который, в свою очередь, открывает каждый результирующий набор и записывает результаты в таблицы, которые вы назначаете (или делаете все, что хотите, с помощью наборов результатов). вы также можете сделать это в DMO, если вам нравится боль.

Ответ 6

Есть два способа сделать это легко. Либо верните результаты в таблицу темпа, а затем обратитесь к таблице temp из вашего sproc. Другой альтернативой является представление результатов в переменную XML, которая используется как переменная OUTPUT.

Тем не менее, существуют и плюсы и минусы обоих этих вариантов. С временной таблицей вам нужно добавить код в script, который создает процедуру вызова для создания временной таблицы перед изменением процедуры. Кроме того, вы должны очистить таблицу temp в конце процедуры.

В XML это может быть интенсивным и медленным.

Ответ 7

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