, table, . , , , Transact-SQL . SELECT, , .
, , , . , , FROM Transact-SQL, , .
CREATE FUNCTION function_name
([ { @parameter_name [ AS ] parameter_data_type
[ = default ] }
[,...n ]
]
)
RETURNS @return_variable TABLE < table_type_definition >
[ WITH [ ENCRYPTION ] [ SCHEMABINDING ] [ EXECUTE AS Clause ]]
[ AS ]
BEGIN
function_body
RETURN
END
<table_type_definition>:: =
(
{ <column_definition> <column_constraint>
| <computed_column_definition> }
[ <table_constraint> ] [,...n ]
)
function_name
.
@ parameter_name
. .
1 024 . , .
@. , . . , .
Parameter_data_type
. Transact-SQL timestamp, cursor table .
[ = default ]
. default, , .
, DEFAULT . DEFAULT , .
<table_type_definition>
, .
ENCRYPTION
|
|
|
, Database Engine CREATE FUNCTION . , , .
SCHEMABINDING
, , . SCHEMABINDING , , . , , .
, , :
.
ALTER, SCHEMABINDING.
EXECUTE AS
, , .. Database Engine , . , .
, , . , , , .
EXECUTE AS { CALLER | SELF | OWNER | 'user_name' }.
Function_body
Transact-SQL, .
, , function_body Transact-SQL, @return_variable.
TABLE
, , , . , , .
, , @ return_variable TABLE, , .
Transact-SQL . , , , , TABLE, RETURN.
:
ufnGetUserInfo. @retUserInfo. , .
CREATE FUNCTION ufnGetUserInfo (@LoginName nvarchar(50))
RETURNS @retUserInfo TABLE
(
UserID int PRIMARY KEY NOT NULL,
FirstName nvarchar(50) NULL,
JobTitle nvarchar(2);
)
AS
BEGIN
DECLARE
@UserID nvarchar(50),
@FirstName nvarchar(50),
@JobTitle nvarchar(2);
SELECT
@UserID = UserID,
@FirstName = FirstName
FROM User
|
|
|
WHERE LoginName = @LoginName;
IF @UserID IS NOT NULL
BEGIN
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.UserID = @UserID AND p.PersonType = 'EM')
THEN (SELECT JobTitle
FROM Employee AS e
WHERE e.UserID = @UserID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.UserID = @UserID AND p.PersonType = 'PT')
THEN (SELECT JobTitle
FROM Partner AS pt
WHERE pt.UserID = @UserID)
ELSE NULL
END;
END;
IF @UserID IS NOT NULL
BEGIN
INSERT @retUserInfo
SELECT @UserID, @FirstName, @JobTitle;
END;
RETURN;
END;
SELECT UserID, FirstName, JobTitle
FROM ufnGetUserInfo('admin');
