Denali CTP 1 – co nowego nie tylko dla deweloperów – cz. 2

Udostępnij na: Facebook

Autor: Damian Widera, Łukasz Grala

Opublikowano: 2010-11-30

Deweloperskich nowości ciąg dalszy

Prezentujemy kolejną porcję informacji dotyczących nowości dostępnych w SQL Server „Denali” CTP1 – pokazane zostaną sposoby korzystania z typowanych tabel.

Typowane tabele

Jednym z problemów, z jakimi borykali się do tej pory deweloperzy aplikacji bazodanowych, był brak mechanizmu tzw. typowanych tabel. Typowane tabele zwracane są jako rezultat działania zapytania TSQL, w tym procedury składowanej. Ściślej mówiąc, można w SQL Server „Denali” CTP1 określić dokładnie, co (z dokładnością do kolumny oraz typu danych) zostanie zwrócone do aplikacji klienckiej.

Można więc zapisać przykład,  gdzie wywołane zostaje dynamiczne zapytanie SQL, którego wynik jest następnie w odpowiedni sposób formatowany:

Rys.  1. Przykład użycia  komendy WITH RESULT SET.

Skład nie RESULT SETS posiada jeszcze dwie dodatkowe opcje:

  • NONE, gwarantującej, że nie zostanie zwrócony żaden wynik,
  • UNDEFINED, która określa, ż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ń i ta opcja może być pomijana.

Idąc dalej – definicje tabeli typowanej można rzeczywiście zapisać w tabeli lub w typie tabelarycznym, a następnie zapisać (wykonać) zapytanie:

CREATE TABLE Tabela (                 id_obiektu INT,                 nazwa VARCHAR(250) ) GO CREATE TYPE Typ AS TABLE (                 id_obiektu INT,                 nazwa VARCHAR(250) ) GO DECLARE @strVARCHAR(MAX) SET @str = 'SELECT object_id, name FROM master.sys.tables' EXEC (@str) WITH RESULT SETS (                 AS OBJECT dbo.Tabela ) EXEC (@str) WITH RESULT SETS (                 AS TYPE dbo.Typ )

Wynik wykonania zapytania będzie identyczny z pokazanym na rysunku nr 1. Należy pamiętać, że tabela dbo.Tabela po wykonaniu zapytania nie będzie zawierała tych danych, ponieważ służy ona tylko do przechowania definicji zwracanego wyniku.

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 rysunku 2.

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

Pod adresem https://msdn.microsoft.com/en-us/library/ms188332(v=SQL.110).aspx znajdziemy kolejne ciekawe zastowanie składni RESULT SET. Można jej bowiem użyć także w procedurach składowanych. Postępowanie w takich przypadkach jest analogiczne do wykonywanego obecnie. Nic się nie zmienia w trakcie pisania kodu procedury składowanej. W omawianym przypadku tworzona jest najpierw procedura Production.ProductList, która zwraca dwie tabele do aplikacji klienckiej. Uruchamiając procedurę, można zdefiniować postać wyniku dla każdej z tabel, jak pokazano na przykładzie poniżej:

USE AdventureWorks2008R2; GO --Create the procedure CREATE PROC Production.ProductList @ProdNamenvarchar(50) AS -- First result set SELECT ProductID, Name, ListPrice     FROM Production.Product     WHERE Name LIKE @ProdName; -- Second result set SELECT Name, COUNT(S.ProductID) AS NumberOfOrders     FROM Production.Product AS P     JOIN Sales.SalesOrderDetail AS S         ON P.ProductID  =S.ProductID     WHERE Name LIKE @ProdName GROUP BY Name; GO -- Execute the procedure EXEC Production.ProductList '%tire%' WITH RESULT SETS (     (ProductIDint,   -- first result set definition starts here     Name Name, ListPrice money)     ,                 -- comma separates result set definitions     (Name Name,       -- second result set definition starts here NumberOfOrdersint) );

Widoki systemowe zwracające metadane dla wyników zapytań

SQL Server „Denali” CTP1 posiada kilka dynamicznych widoków zarządczych rozpoczynających się od słowa sys.dm_exec_describe_*, które opisują wyniki zapytań. Ich użycie na pewno ułatwi zadania programistyczne, ponieważ wyeliminuje np. konieczność rzutowania wyników.

Rozważmy przykład pokazany na rysunku 3. Dla zapytania SELECT * FROM authors z bazy danych pubs wygenerowany jest wynik opisujący, co zostanie zwrócone do aplikacji klienckiej po wykonaniu tego zapytania. Można odczytać pozycję kolumny w wyniku, jej typ, nazwę oraz inne atrybuty, których określenie do tej pory wiązało się z koniecznością napisania własnego fragmentu kodu.

Rys.  3. Użycie dynamicznego widoku sys.dm_exec_describe_first_result_set.

Używając dynamicznego widoku zarządczego sys.dm_exec_describe_first_result_set_for_object, można uzyskać informacje o wynikach zwracanych np. z procedury składowanej. Poniżej pokazano, jak użyć tego widoku dla odczytania informacji z procedury składowanej dbo.byroyalty:

select * from sys.dm_exec_describe_first_result_set_for_object( OBJECT_ID('dbo.byroyalty'),0)

Po uruchomieniu powyższego fragmentu kodu w bazie danych pubs uzyskujemy następujący rezultat:

Rys.  4. Wynik użycia dynamicznego widoku zarządczego sys.dm_exec_describe_first_result_set_for_object.

Ostatnią prezentowaną w dzisiejszym artykule nowością jest procedura składowana sp_describe_undeclared_parameters, która pozwala znaleźć informacje na temat niezadeklarowanych parametrów użytych w podanym zapytaniu. Składnia tej procedury jest następująca:

sp_describe_undeclared_parameters     [ @tsql = ] 'Transact-SQL_batch'     [ , [ @params = ] N'parameters' data type ] [, ...n]

Uzyskana informacja będzie dotyczyła tych parametrów, które znajdują się w zmiennej @tsql, a nie są zadeklarowane w zmiennej @params.

Na rysunku 5 pokazano przykład użycia tej procedury, w którym nie został zadeklarowany parametr @au\_id. Informacja zwrotna pozwala okreslić jego sugerowany typ, rozmiar, precyzję, skalę itd.

Rys.  5. Użycie systemowej procedury składowanej  sp_describe_undeclared_parameters.