Nowości w SQL Server 2012 – WITH RESULT SETS

2012-05-30 

W SQL Server 2012 polecenie EXECUTE rozszerzone zostało o dodatkową opcję WITH RESULT SETS. Pozwala ona na określenie oczekiwanej struktury danych zwracanych przez zapytanie lub procedurę składowaną. Jeżeli zwrócony wynik nie będzie zgodny z określoną definicją wygenerowany zostanie wyjątek. Omawiana opcja umożliwia także nadanie własnych nazw kolumnom wynikowego zbioru danych. Klauzula WITH RESULT SETS może przyjąć trzy opcje:

  • WITH RESULT SETS UNDEFINED – nieokreślona struktura danych wynikowych, jest to ustawienie domyślne
  • WITH RESULT SETS NONE – brak wynikowego zbioru danych, jeżeli operacja zwróci jakikolwiek zbiór wygenerowany zostanie wyjątek
  • WITH RESULT SETS <result_sets_definition> – definicja struktury wynikowego zbioru danych

Definicja wyniku (<result_sets_definition>) może być określona na cztery sposoby:

  • AS OBJECT – struktura danych wynikowych musi pokrywać się z danym obiektem (tabelą, widokiem, funkcją zwracającą tabelę)
  • AS TYPE – struktura danych wynikowych musi pokrywać się z danym typem tabelarycznym
  • AS FOR XML – dane zwracane przez polecenie muszą mieć format XML
  • <custom_definition> – własna struktura danych wynikowych (lista kolumn wraz z typami oraz dodatkowymi informacjami: null/not null, collate)

W wyniku wykonania polecenia EXECUTE może zostać zwróconych kilka zbiorów danych. Za pomocą WITH RESULT SETS możemy określić ich liczbę oraz strukturę każdego z nich. Co więcej, możemy w takim przypadku stosować różne typy definicji. Można na przykład określić, że dana procedura składowana musi zwrócić dwa zbiory danych, z czego pierwszy będzie miał strukturę zgodną z typem tabelarycznym, a drugi będzie w formacie XML.

W celu zaprezentowania działania opcji WITH RESULT SETS stworzyłem tabelę Obiekty, typ tabelaryczny tObiekty oraz procedurę składowaną PobierzObiekty. Procedura przyjmuje parametr typu int i w zależności od jego wartości zwraca różne wyniki (0 – brak zbioru wynikowego, 1 – jeden zbiór danych, 2 – dwa zbiory danych, 3 – wynik w formacie XML):

--Utworzenie tabeli Obiekty
create table Obiekty
(
    Id int not null,
    Nazwa nvarchar(512)
)

--Wstawienie 10-ciu rekordów
insert into Obiekty (Id, Nazwa)
select top 10 object_id, name
from sys.objects
order by object_id

--Utworzenie typu tabelarycznego tObiekty
create type tObiekty
as table
(
    Id int not null,
    Nazwa nvarchar(512)
)

--Utworzenie procedury PobierzObiekty
create procedure PobierzObiekty
    @wynik int
as
begin
    --0 - brak wyników
    if @wynik = 0
        return
    --1 - jeden zbiór wynikowy, 2 - dwa zbiory wynikowe
    if @wynik in (1, 2)
    begin
        select Id, Nazwa
        from Obiekty

        if @wynik  = 2
            select object_id, name, create_date
            from sys.objects

        return
    end
    --3 - wynik w formacie XML
    if @wynik = 3
    begin
        select Id, Nazwa
        from Obiekty as Obiekt
        for xml auto, root('Obiekty')

        return
    end
end

Przykłady użycia WITH RESULT SETS dla zapytania oraz procedury składowanej:

--Definicja wyniku dla zapytania
exec ('select * from Obiekty')
with result sets
(
    (
        Id int not null,
        Nazwa nvarchar(50)
    )
)

--Definicja wyniku dla procedury
exec PobierzObiekty 1
with result sets
(
    (
        Id int not null,
        Nazwa nvarchar(50)
    )
)

--Procedura nie zwracająca danych
exec PobierzObiekty 0
with result sets none

--Dowolny rodzaj wyniku lub jego brak
exec PobierzObiekty 1
with result sets undefined

--Definicja wyniku na podstawie tabeli
exec PobierzObiekty 1
with result sets
(
    as object Obiekty
)

--Definicja wyniku na podstawie typu tabelarycznego
exec PobierzObiekty 1
with result sets
(
    as type tObiekty
)

--Wynik w formacie XML
exec PobierzObiekty 3
with result sets
(
    as for xml
)

Przykłady użycia WITH RESULT SETS dla kilku zbiorów danych zwracanych przez procedurę składowaną z jednoczesną zmianą nazw kolumn tych zbiorów:

--Własne definicje dwóch wynikowych zbiorów danych
exec PobierzObiekty 2
with result sets
(
    (
        Identyfikator int not null,
        MojaNazwa nvarchar(50)
    ),
    (
        Identyfikator int not null,
        MojaNazwa nvarchar(50),
        DataUtworzenia datetime
    )
)

--Definicja na podstawie typu oraz własna
exec PobierzObiekty 2
with result sets
(
    as type tObiekty,
    (
        Identyfikator int not null,
        MojaNazwa nvarchar(50),
        DataUtworzenia datetime
    )
)

Jeżeli struktura zwracanych danych nie będzie zgodna z określoną definicją wygenerowany zostanie wyjątek (sprawdzenie nie dotyczy nazw kolumn):

--EXECUTE statement failed because its WITH RESULT SETS clause specified a non-nullable type for column #1 in result set #1, and the corresponding value sent at run time was null.
exec ('select null as Id, Nazwa from Obiekty')
with result sets
(
    (
        Id int not null,
        Nazwa nvarchar(50)
    )
)

--EXECUTE statement failed because its WITH RESULT SETS clause specified 0 result set(s), and the statement tried to send more result sets than this.
exec ('select * from Obiekty')
with result sets none

W pierwszym przypadku problemem są wartości NULL w kolumnie Id, w drugim zapytanie zwraca zbiór danych mimo, iż definicja na to nie pozwala.

Więcej informacji o poleceniu EXECUTE oraz opcji WITH RESULT SETS: MSDN

Reklamy

Posted on 2012-05-30, in SQL Server and tagged , , . Bookmark the permalink. Dodaj komentarz.

Skomentuj

Wprowadź swoje dane lub kliknij jedną z tych ikon, aby się zalogować:

Logo WordPress.com

Komentujesz korzystając z konta WordPress.com. Wyloguj / Zmień )

Zdjęcie z Twittera

Komentujesz korzystając z konta Twitter. Wyloguj / Zmień )

Zdjęcie na Facebooku

Komentujesz korzystając z konta Facebook. Wyloguj / Zmień )

Zdjęcie na Google+

Komentujesz korzystając z konta Google+. Wyloguj / Zmień )

Connecting to %s

%d blogerów lubi to: