Funkcje oródwierszową zdefiniowaną przez użytkownika
Zdefiniowane przez użytkownika funkcje zwracające table Typ danych może być wydajne rozwiązania alternatywne do widoków.Funkcje te są zwane tabela-wycenione funkcji.Oródwierszową zdefiniowaną przez użytkownika funkcja mogą być używane, gdy wyrażenia tabela lub widoku są dozwolone w Transact-SQL kwerend.Widoki są ograniczone do pojedynczej instrukcja SELECT, funkcje zdefiniowane przez użytkownika mogą zawierać dodatkowe instrukcje, które umożliwiają logiki bardziej wydajne niż jest to możliwe w widokach.
A tabela-ważnych funkcji zdefiniowanej przez użytkownika można także zastąpić procedur przechowywanych, które zwracają zestaw pojedynczy wynik.Tabela zwrócona przez funkcja zdefiniowaną przez użytkownika można odwoływać się w klauzula FROM Transact-SQL instrukcja, ale procedur przechowywanych, które zwracają zestaw wyników nie.
Składniki funkcją oródwierszową zdefiniowaną przez użytkownika
W tabela-wycenione funkcja zdefiniowana przez użytkownika:
Klauzula zwraca definiuje zwrotu lokalne nazwy zmiennej tabela zwrócone przez funkcja.Klauzula zwraca również definiuje format tabela.Zakres lokalny zwrotu nazwa zmiennej jest lokalny wewnątrz funkcja.
Transact-SQL Instrukcji w treści funkcja tworzenia i wstawia wiersze do zwrotu zmiennej zdefiniowane przez zwraca klauzula.
Podczas wykonywania instrukcja RETURN wiersze wstawione do zmiennej są zwracane jako produkcja tabelarycznej funkcja.Instrukcja RETURN nie może mieć argument.
Nie Transact-SQL instrukcji w funkcja zwracająca tabelę można zwrócić zestaw wyników bezpośrednio do użytkownika.Jedyną informacją, funkcja może zwrócić się do użytkownika jest table zwrócone przez funkcję.
Ostrzeżenie
text in row Opcja tabela jest automatycznie zestaw do 256 tabela zwrócone przez funkcja zdefiniowaną przez użytkownika.Nie można zmienić.Nie można użyć instrukcji READTEXT, WRITETEXT i UPDATETEXT do odczytu lub zapisu dowolnej części text, ntext, lub image kolumn w tabela.Aby uzyskać więcej informacji, zobacz W wierszu danych.
Przykład
Poniższy przykład tworzy dbo.ufnGetContactInformation funkcja i demonstruje składników funkcja zwracająca tabelę.Funkcja ta jest lokalna nazwa zmiennej zwrotu @retContactInformation.Instrukcje w treści funkcja Wstaw wiersze do tej zmiennej do budowania wynik tabela zwrócone przez funkcja.
USE AdventureWorks2008R2;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
ContactID int PRIMARY KEY NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
JobTitle nvarchar(50) NULL,
ContactType nvarchar(50) NULL
)
AS
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
DECLARE
@FirstName nvarchar(50),
@LastName nvarchar(50),
@JobTitle nvarchar(50),
@ContactType nvarchar(50);
-- Get common contact information
SELECT
@ContactID = BusinessEntityID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Person
WHERE BusinessEntityID = @ContactID;
-- Get contact job title
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'EM')
THEN (SELECT JobTitle
FROM HumanResources.Employee AS e
WHERE e.BusinessEntityID = @ContactID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'VC')
THEN (SELECT ct.Name
FROM Person.ContactType AS ct
INNER JOIN Person.BusinessEntityContact AS bec
ON bec.ContactTypeID = ct.ContactTypeID
WHERE bec.PersonID = @ContactID)
-- Check for store
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'SC')
THEN (SELECT ct.Name
FROM Person.ContactType AS ct
INNER JOIN Person.BusinessEntityContact AS bec
ON bec.ContactTypeID = ct.ContactTypeID
WHERE bec.PersonID = @ContactID)
ELSE NULL
END;
-- Get contact type
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'EM')
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'VC')
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'SC')
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'IN')
THEN 'Consumer'
-- Check for general contact
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'GC')
THEN 'General Contact'
END;
-- Return the information to the caller
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
END;
RETURN;
END;
GO
W poniższym przykładzie użyto funkcja zwracająca tabelę dbo.ufnGetContactInformation w klauzula FROM dwóch instrukcji SELECT.
USE AdventureWorks2008R2;
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(1209);
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(5);
GO