Funkcje zdefiniowane przez użytkownika są skryptami T-SQL lub CLR które potrafią przyjmować parametry i zwracać wyniki skalarne lub tablicowe. Celem tworzenia funkcji definiowanych przez użytkownika (UDF) jest enkapsulacja kodu T-SQL który może być używany wielokrotnie. Tak jak procedury składowane funkcje użytkownika mogą przyjmować parametry które mogą być używane w ciele funkcji jak zmienne. W odróżnieniu od procedur,  wykonanie funkcji jest osadzone w kodzie T-SQL. Funkcje UDF nie mogą być wykonywane za pomocą komendy EXECUTE. Funkcje UDF pobierają dane z SQL Serwera lecz nie mogą wykonywać żadnych zadań modyfikacji tabel języka DDL i danych języka DML. Istnieją dwa główne rodzaje funkcji użytkownika: skalarne i tablicowe. Skalarne zwracają pojedynczą wartość a tablicowe zwracają wynik tablicowy. Funkcje tablicowe mogą być używane w klauzuli FROM. Funkcje tablicowe które mogą być zapisane w jednej lini kodu T-SQL są zwane funkcjami inline table-valued UDF. Funkcje tablicowe które nie mogą być zapisane w jednej lini kodu T-SQL są zwane multistatement table-valued UDF. Kiedy odnosimy się do typu funkcji w kolumnie type tabeli sys.objects lub parametru funkcji OBJET_ID() to istnieją trzy oznaczenia dla funkcji:

• FN - funkcja skalarna.
• IF - funkcja tablicowa inline.
• TF - funkcja tablicowa.

Funkcje skalarne

Funkcje skalarne zwracają wynik skalarny. Mogą występować w każdym fragmencie zapytania gdzie wyrażenie zwracające pojedynczą wartość może się pojawić (np w klauzuli SELECT). Kod zawarty w ciele funkcji skalarnej zawsze otoczony jest blokiem BEGIN/END. Używając SSMS Insert Snippet wstawiamy funkcję skalarną w taki sposób:

CREATE FUNCTION dbo.FunctionName
(
@param1 int,
@param2 int
)
RETURNS INT
AS
BEGIN
RETURN @param1 + @param2
END

Przerabiając ten Snippet stwórzmy funkcję skalarną obliczającą iloczyn ceny i ilości dla tabeli Sales:

IF OBJECT_ID('Sales.fn_extension', 'FN') IS NOT NULL
DROP FUNCTION Sales.fn_extension
GO
CREATE FUNCTION Sales.fn_extension
(
@unitprice AS MONEY,
@qty AS INT
)
RETURNS MONEY
AS
BEGIN
RETURN @unitprice * @qty
END;
GO

Cena jednostkowa i ilość są parametrami funkcji. Wywołanie funkcji w klauzuli SELECT wygląda tak:

SELECT Orderid
,unitprice
,qty
,Sales.fn_extension(unitprice, qty) AS extension
FROM Sales.OrderDetails;

Wywołanie tej funkcji w klauzuli WHERE do ograniczenia listy wynikowej której iloczyn > 1000:

SELECT Orderid
,unitprice
,qty
,Sales.fn_extension(unitprice, qty) AS extension
FROM Sales.OrderDetails
WHERE Sales.fn_extension(unitprice, qty) > 1000;

Funkcje tablicowe

Funkcje tablicowe zwracają tabele zamiast wartości skalarnej. Funkcja tablicowa może być używana wszędzie tam gdzie tabela jest dopuszczalna czyli np w klauzuli FROM. Funkcje tablicowe inline są jedynym typem funkcji UDF które mogą być zapisane bez bloku BEGIN/END. Funkcje tablicowe multistatment muszą mieć klauzulę RETURN na końcu ciała funkcji.

Funkcje tablicowe inline

Funkcje tablicowe inline zawierają pojedyncze wyrażenie SELECT które zwraca w wyniku tabelę. Używając SSMS Insert Snippet wstawiamy funkcję inline w taki sposób:

CREATE FUNCTION dbo.FunctionName
(
@param1 int,
@param2 char(5)
)
RETURNS TABLE AS RETURN
(
SELECT @param1 AS c1,
@param2 AS c2
)

Zmodyfikujmy Snippet tak aby powstała funkcja zwracająca wiersze tabeli Sales.OrderDetails które posiadają ilość pomiędzy dwiema wartościami które to będą wprowadzane jako parametry funkcji:

IF OBJECT_ID('Sales.fn_FilteredExtension', 'IF') IS NOT NULL
DROP FUNCTION Sales.fn_FilteredExtension;
GO
CREATE FUNCTION Sales.fn_FilteredExtension
(
@lowqty AS SMALLINT,
@highqty AS SMALLINT
)
RETURNS TABLE AS RETURN
(
SELECT orderid, unitprice, qty
FROM Sales.OrderDetails
WHERE qty BETWEEN @lowqty AND @highqty
);
GO

Aby wywołać funkcję w klauzuli FROM dla ilości pomiędzy 10 a 20 należy użyć zapytania:

SELECT orderid, unitprice, qty
FROM Sales.fn_FilteredExtension (10,20);

Ponieważ funkcja inline nie wykonuje żadnych operacji poza SELECT to optymalizator traktuje ją jak widok. Można użyć na niej również instrukcji INSERT, UPDATE lub DELETE tak samo jak przy użyciu widoku. Dlatego można myśleć o funkcjach inline jak o widoku z parametrami.

Funkcje tablicowe multistatement

Składnia funkcji multistatement różni się nieznacznie od funkcji inline. SSMS code snippet wygląda dla niej tak:

CREATE FUNCTION dbo.FunctionName
(
@param1 int,
@param2 char(5)
)
RETURNS @returntable TABLE
(
c1 int,
c2 char(5)
)
AS
BEGIN
INSERT @returntable
SELECT @param1, @param2
RETURN
END;
GO

Różnicą jest to, że trzeba na początku funkcji zdefiniować zmienną tabelaryczną która będzie wynikiem działania funkcji i wypełnić ją żądanymi danymi w ciele funkcji. Komenda RETURN kończy funkcję. Przetworzenie poprzednio utworzonej funkcji inline na funkcję multistatement wygląda tak:

IF OBJECT_ID('Sales.fn_FilteredExtension2', 'TF') IS NOT NULL
DROP FUNCTION Sales.fn_FilteredExtension2;
GO
CREATE FUNCTION Sales.fn_FilteredExtension2
(
@lowqty AS SMALLINT,
@highqty AS SMALLINT
)
RETURNS @returntable TABLE
(
orderid INT,
unitprice MONEY,
qty SMALLINT
)
AS
BEGIN
INSERT @returntable
SELECT orderid, unitprice, qty
FROM Sales.OrderDetails
WHERE qty BETWEEN @lowqty AND @highqty
RETURN
END;
GO

Wywołanie jest takie samo jak funkcji inline np:

SELECT orderid, unitprice, qty
FROM Sales.fn_FilteredExtension2 (10,20);

Ograniczenia UDF

Użytkownik tworzący funkcje potrzebuje uprawnień CREATE FUNCTION w bazie danych. Ograniczenia UDF:

• Funkcje nie mogą zmieniać schematu bazy danych ani żadnych danych.
• Funkcje nie mogą zmieniać stanu bazy danych.
• Nie mają dostępu do tabel tymczasowych.
• Nie mogą tworzyć tabel tymczasowych.
• Nie mogą wywoływać procedur składowanych.
• Nie mogą wykonywać kodu dynamicznego.

Opcje UDF

• ENCRYPTION - ta opcja zakodowuje kod funkcji w bazie danych.
• SCHEMABINDING - opcja która wiąże schematy wszystkich powiązanych obiektów.
• RETURNS NULL ON | NULL INPUT - jeżeli ta opcja jest ustawiona to wartość jakiegokolwiek parametru wynosząca NULL powoduje, że UDF zwraca NULL bez wykonywania kodu.
• CALLED ON NULL INPUT - jest to opcja domyślna i powoduje, że ciało funkcji skalarnej będzie wykonane jeżeli jako parametr zostanie przekazana wartość NULL.
• EXECUTE AS - oznacza wykonanie w różnych kontekstach.

Funkcje UDF mogą być zagnieżdżone.

Ćwiczenia

I.Funkcja skalarna obliczająca zniżkę

1. Napisz zapytanie wyciągające koszt, ilość i zniżkę z tabeli Sales.SalesOrder.

   SELECT orderid
   , productid
   , unitprice
   , qty
   , discount
   FROM Sales.OrderDetails;
   

2. Do zapytania dodaj wynik całkowitego kosztu oraz całkowitego kosztu po zniżce.

   SELECT orderid
   , productid
   , unitprice
   , qty
   , discount
   , unitprice * qty as totalcost
   , (unitprice * qty) * (1 - discount) as costafterdiscount
   FROM Sales.OrderDetails;
   

3. Napisz funkcję wyliczającą koszt po zniżce.

   IF OBJECT_ID('Sales.fn_CostAfterDiscount', 'FN') IS NOT NULL
   DROP FUNCTION Sales.fn_CostAfterDiscount;
   GO
   CREATE FUNCTION Sales.fn_CostAfterDiscount(
   @unitprice AS MONEY,
   @qty AS SMALLINT,
   @discount AS NUMERIC(4,3)
   ) RETURNS MONEY
   AS
   BEGIN
   RETURN (@unitprice * @qty) * (1 - @discount);
   END;
   GO
   

4. Użyj funkcji w zapytaniu.

   SELECT Orderid
   , unitprice
   , qty
   , discount
   , Sales.fn_CostAfterDiscount(unitprice, qty, discount) AS costafterdiscount
   FROM Sales.OrderDetails;
   

5. Usuń funkcję.

   IF OBJECT_ID('Sales.fn_CostAfterDiscount', 'FN') IS NOT NULL
   DROP FUNCTION Sales.fn_CostAfterDiscount;
   GO
   

II. Tworzenie tablicowej funkcji UDF

1. Napisz zapytanie które pobiera cenę, ilość i ich iloczyn z tabeli Sales.SalesOrder.

   SELECT orderid, unitprice, qty, (unitprice * qty) AS extension
   FROM Sales.OrderDetails;
   

2. Napisz zapytanie filtrujące zamówienia z tabeli Sales.SalesOrder wg minimalnej i maksymalnej ilości. Użyj zmiennych do zdefiniowania minimalnej i maksymalnej ilości.

   DECLARE @lowqty AS SMALLINT = 10
   , @highqty AS SMALLINT = 20;
   SELECT orderid, unitprice, qty, (unitprice * qty) AS extension
   FROM Sales.OrderDetails
   WHERE qty BETWEEN @lowqty AND @highqty;
   

3. Stwórz funkcję z zapytania.

   IF OBJECT_ID('Sales.fn_FilteredExtension', 'FN') IS NOT NULL
   DROP FUNCTION Sales.fn_FilteredExtension;
   GO
   CREATE FUNCTION Sales.fn_FilteredExtension
   (
   @lowqty AS SMALLINT,
   @highqty AS SMALLINT
   )
   RETURNS TABLE AS RETURN
   (
   SELECT orderid, unitprice, qty, (unitprice * qty) AS extension
   FROM Sales.OrderDetails
   WHERE qty BETWEEN @lowqty AND @highqty
   );
   GO
   

4. Przetestuj funkcję.

   SELECT *
   FROM Sales.fn_FilteredExtension (10,20);
   

5. Usuń funkcję.

   IF OBJECT_ID('Sales.fn_FilteredExtension', 'FN') IS NOT NULL
   DROP FUNCTION Sales.fn_FilteredExtension;
   GO
   

Podsumownie

1. Funkcje UDF enkapsulują kod T-SQL który może być użyty ponownie do zwrócenia wartości skalarnej lub tablicowej.
2. Funkcje akceptują parametry.
3. Funkcje tylko pobierają dane. Nie mogą modyfikować danych ani schematu bazy danych.
4. Istnieją dwa główne typy funkcji UDF: skalarne i tablicowe.