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 GOZapytanie 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 GOJednak 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 ENDFunkcja 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 GOTo 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.
