SQL Server 2012 - Typowane tabele  

Autor: Damian Widera

Opublikowano: 2012-12-05

Brak mechanizmu tzw. typowanych tabel to jednym z problemów, z jakimi do tej pory borykali się deweloperzy aplikacji bazodanowych. Typowane tabele zwracane są jako rezultat działania zapytania TSQL, w tym procedury składowanej.

Można więc dokładnie (co do kolumny oraz typu danych) określić, co zostanie zwrócone do aplikacji klienckiej.

Poniższy przykład pokazuje, w jaki sposób sformatować dynamiczne zapytanie TSQL, tak aby zwróciło wynik odpowiedniego typu:

EXEC('SELECT name,
    OBJECT_DEFINITION([object_id]) FROM sys.procedures')
WITH RESULT SETS
(
    (
        name SYSNAME,
        body XML
    )
);

Składnia RESULT SETS posiada jeszcze dwie dodatkowe opcje:

• NONE, która gwarantuje, że nie zostanie zwrócony żaden wynik,
• UNDEFINED, która informuje, że wynik może lub nie musi zostać zwrócony. W przypadku, gdy jakieś dane zostaną przekazane do aplikacji klienckiej, to nie ma pewności, co do ich definicji. Jest to domyślne zachowanie dla zapytań, więc ta opcja może zostać pominięta.

Definicja wyniku, który zostanie zwrócony może zostać zrealizowana na cztery sposoby:

• AS OBJECT – struktura danych wynikowych musi być zbieżna z obiektem, np. tabelą, widokiem lub funkcją, która zwraca tabelę,
• AS TYPE – struktura danych wynikowych musi być identyczna do zdefiniowanego typu tabelarycznego,
• AS FOR XML – zwracane dane muszą być typu XML.

Składnia RESULT SET może być również używana z procedurami składowanymi. W takim wypadku, wynik działania procedury składowanej jest formatowany, co zaprezentowano na przykładzie, poniżej:

use AdventureWorks2012
go
EXEC uspGetEmployeeManagers 16
WITH RESULT SETS
( 
   ([Reporting Level] int NOT NULL,
    [ID of Employee] int NOT NULL,
    [Employee First Name] nvarchar(50) NOT NULL,
    [Employee Last Name] nvarchar(50) NOT NULL,
    [Employee ID of Manager] hierarchyid  NOT NULL,
    [Manager First Name] nvarchar(50) NOT NULL,
    [Manager Last Name] nvarchar(50) NOT NULL )
);

Powyższy przykład można zmodyfikować w taki sposób, aby dane typu tabelarycznego były zwracane. Pierwszym krokiem jest zdefiniowanie odpowiedniego typu tabelarycznego:

CREATE TYPE employee_type as TABLE
(
    [Reporting Level] int NOT NULL,
    [ID of Employee] int NOT NULL,
    [Employee First Name] nvarchar(50) NOT NULL,
    [Employee Last Name] nvarchar(50) NOT NULL,
    [Employee ID of Manager] nvarchar(50)  NOT NULL, 
    [Manager First Name] nvarchar(50) NOT NULL,
    [Manager Last Name] nvarchar(50) NOT NULL 
)

Następnie, modyfikowane jest wywołanie procedury składowanej do następującej postaci:

EXEC uspGetEmployeeManagers 16
WITH RESULT SETS
( 
  as TYPE employee_type
);

Jeżeli mamy zapytanie, zwracające dane w formacie XML, to można również skorzystać z klauzuli RESULTS SETS, np. w następujący sposób:

EXEC ('
        SELECT * 
        FROM Production.Product P
        WHERE p.ProductID =979
        FOR XML AUTO, ROOT (''PRODUCT'')
     ')
WITH RESULT SETS
(
    AS FOR XML
)

Co zrobić, jeśli zapytanie zwraca dwa wyniki? Wystarczy przypomnieć sobie działanie systemowej procedury sp_help, która, przyjmując jako parametr nazwę obiektu, zwraca informacje na jego temat. Informacje te są podzielone na dwie tabele. Dzięki zastosowaniu RESULT SET można i w tym przypadku odpowiednio przygotować informacje wyjściowe, jak pokazano na Rys. 1.

Rys. 1. Działanie systemowej procedury sp_help z użyciem składni RESULT SET.