Pobieranie danych z tabeli o dynamicznej nazwie (Dynamic SQL lub funkcja zwracająca tabelę)

17-Sty-2011

Załóżmy, że w bazie danych mamy tabele o nazwach Total2001, Total2002, Total2003 itd. Generalnie nazwa tabeli utworzona jest poprzez złączenie (sklejenie) słowa ‘Total’ i roku.

Załóżmy też, że naszym zadanie  jest regularne tworzenie skomplikowanych procedur lub zapytań, które w zależności od warunków powinny pracować bądź na jednej, bądź na innej tabeli.

W naszym przypadku popracujemy na bazie danych AdventureWorks2008,a tym skomplikowanym zapytaniem będzie SELECT pobierający dane z tabeli TotalYYYY i złączający je z tabelą Products.

Do rozwiązania problemu można użyć tzw. Dynamiczny SQL (Dynamic SQL) lub funkcję zwracającą tabelę.

Zacznijmy od utworzenia tabel TotalYYYY:

USE AdventureWorks2008;
GO
 
–Tworzy tabele Total2001
SELECT
      DATEPART(yyyy,soh.OrderDate) AS YearOrderDatail ,sod.ProductID, SUM(sod.LineTotal) AS SumLineTotal
INTO Total2001
FROM Sales.SalesOrderDetail sod
JOIN Sales.SalesOrderHeader soh
      on soh.SalesOrderID=sod.SalesOrderID
WHERE DATEPART(YYYY,soh.OrderDate) = 2001
GROUP BY DATEPART(yyyy, soh.OrderDate), sod.ProductID
ORDER BY YearOrderDatail, sod.ProductID
GO   
 
–Tworzy tabele Total2002
SELECT
      DATEPART(yyyy,soh.OrderDate) AS YearOrderDatail ,sod.ProductID, SUM(sod.LineTotal) AS SumLineTotal
INTO Total2002
FROM Sales.SalesOrderDetail sod
JOIN Sales.SalesOrderHeader soh
      on soh.SalesOrderID=sod.SalesOrderID
WHERE DATEPART(YYYY,soh.OrderDate) = 2002
GROUP BY DATEPART(yyyy, soh.OrderDate), sod.ProductID
ORDER BY YearOrderDatail, sod.ProductID
GO   
 
–Tworzy tabele Total2003
SELECT
      DATEPART(yyyy,soh.OrderDate) AS YearOrderDatail ,sod.ProductID, SUM(sod.LineTotal) AS SumLineTotal
INTO Total2003
FROM Sales.SalesOrderDetail sod
JOIN Sales.SalesOrderHeader soh
      on soh.SalesOrderID=sod.SalesOrderID
WHERE DATEPART(YYYY,soh.OrderDate) = 2003
GROUP BY DATEPART(yyyy, soh.OrderDate), sod.ProductID
ORDER BY YearOrderDatail, sod.ProductID
GO   
 
–Tworzy tabele Total2004
SELECT
      DATEPART(yyyy,soh.OrderDate) AS YearOrderDatail ,sod.ProductID, SUM(sod.LineTotal) AS SumLineTotal
INTO Total2004
FROM Sales.SalesOrderDetail sod
JOIN Sales.SalesOrderHeader soh
      on soh.SalesOrderID=sod.SalesOrderID
WHERE DATEPART(YYYY,soh.OrderDate) = 2004
GROUP BY DATEPART(yyyy, soh.OrderDate), sod.ProductID
ORDER BY YearOrderDatail, sod.ProductID
GO   
 

Zapytanie złączające tabelę Total2001 z tabelą Products mogłoby wyglądać tak:

SELECT * FROM Total2001 t
JOIN Production.Product p
ON t.ProductID=p.ProductID
GO   

 Jednak my chcemy, aby zapytanie pracowało nie na tabeli Total 2001, ale na dowolnej spośród tych tabel !

Propozycja 1

Deklarujemy zmienną @MyYear, wpisujemy do niej rok, za który ma być wykonane zapytanie, tworzymy zapytanie w postaci napisu (string) i interpretujemy ten napis komendą EXECUTE.

 –Zmienna na tekst zapytania SQL

DECLARE @Command NVARCHAR(MAX);
–Zmienna na parametr rok
DECLARE @MyYear INT;
 
–Tu okreœlam za jaki rok wykonaæ zapytanie
SET @MyYear = 2001;
 
–Tu buduje sie zapytanie SQL
SET @Command = ‚SELECT * FROM Total’ + CAST(@MyYear AS NVARCHAR(4)) + ‚ t
                JOIN Production.Product p
                on t.ProductID=p.ProductID;’;
 
–Dla celow diagnostycznych mozna wyswietlic zbudowane polecenie               
PRINT @Command;               
–Wykonanie polecenia
EXECUTE (@Command); 
–(zwraca dane za rok 2001)
 
–Zmieniam rok
SET @MyYear = 2004;
–Budujê zapytanie
SET @Command = ‚SELECT * FROM Total’ + CAST(@MyYear AS NVARCHAR(4)) + ‚ t
                JOIN Production.Product p
                on t.ProductID=p.ProductID;’;
         
–Wykonanie polecenia
EXECUTE (@Command); 
–(zwraca dane za rok 2004)

 Oczywiście zamiast wykorzystywać EXECUTE, można również użyć Sp_exequtesql – jest to procedura, która nie tylko wykonuje przekazany tekst jako zapytanie SQL, ale potrafi także wykorzystać plan wykonania zapytania w taki sposób, że przy kolejnym odwołaniu do zapytania raz utworzony plan można ponownie wykorzystać.

Wadą tego rozwiązania jest to, że trzeba zawsze budować komendę od początku. Warto by więc było wbudować te instrukcje do procedury składowanej.

Propozycja 2

Budujemy funkcję zwracającą dane z odpowiedniej tabeli:

CREATE FUNCTION f_MyTable(@Year INT)
RETURNS @tab TABLE
(YearOrderDetail INT,
 ProductId INT,
 SumLineTotal MONEY)
 AS
 BEGIN
      IF @Year = 2001
            INSERT INTO @tab SELECT * FROM Total2001;
      IF @Year = 2002
            INSERT INTO @tab SELECT * FROM Total2002;
      IF @Year = 2003
            INSERT INTO @tab SELECT * FROM Total2003;
      IF @Year = 2004
            INSERT INTO @tab SELECT * FROM Total2004;
    RETURN 
 END                    

 Funkcja przyjmuje jako argument wartość roku, a zwraca tabelę os strukturze zgodnej ze strukturą TotalYYYY.

Aby wykorzystać funkcję wystarczy teraz wykonać SELECT na wywołaniu tej funkcji np.:

SELECT * FROM f_MyTable(2001)

Nasze zapytanie może więc wyglądać tak:

SELECT * FROM f_MyTable(2001) t
JOIN Production.Product p
on t.ProductID=p.ProductID
GO

To rozwiązanie nie wymaga budowy polecenia ‘w locie’, niestety, jeżeli w bazie danych pojawi się kolejna tabela np. Total2006, to trzeba obsłużyć tę sytuację w fukcji f_myTable. Niestety funkcja nie może tworzyć tabel, korzystać z tabel tymczasowych, ani nawet wstawiać rekordów zwróconych przez procedurę składowaną do zwracanej przez siebie tabeli.

Dodaj komentarz:

Autor: Rafał Kraik