Transcript Inline table-valued functions

Module 9:
Implementing Functions
Overview
Creating and Using Functions
Working with Functions
Controlling Execution Context
Lesson 1: Creating and Using Functions
Types of Functions
What Is a Scalar Function?
What Is an Inline Table-Valued Function?
What Is a Multi-Statement Table-Valued Function?
Practice: Creating Functions
Types of Functions
Scalar functions
 Similar to built-in functions
 Return a single value
Inline table-valued functions
 Similar to views with parameters
 Return a table as the result of single SELECT statement
Multi-statement table-valued functions
 Similar to stored procedures
 Return a new table as the result of INSERT statements
What Is a Scalar Function?
RETURNS clause specifies data type
Function is defined within a BEGIN…END block
CREATE FUNCTION Sales.SumSold(@ProductID int) RETURNS int
AS
BEGIN
DECLARE @ret int
SELECT @ret = SUM(OrderQty)
FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID
IF (@ret IS NULL)
SET @ret = 0
RETURN @ret
END
Can be invoked anywhere a scalar expression of the same
data type is allowed
SELECT ProductID, Name, Sales.SumSold(ProductID) AS SumSold
FROM
Production.Product
What Is an Inline Table-Valued Function?
RETURNS specifies table as data type
Format is defined by result set
Content of function is a SELECT statement
CREATE FUNCTION HumanResources.EmployeesForManager
(@ManagerId int)
RETURNS TABLE
AS
RETURN (
SELECT
FirstName, LastName
FROM
HumanResources.Employee Employee INNER JOIN
Person.Contact Contact
ON Employee.ContactID = Contact.ContactID
WHERE
ManagerID = @ManagerId )
SELECT * FROM HumanResources.EmployeesForManager(3)
-- OR
SELECT * FROM HumanResources.EmployeesForManager(6)
What Is a Multi-Statement Table-Valued Function?
RETURNS specifies table data type and defines structure
BEGIN and END enclose multiple statements
CREATE FUNCTION HumanResources.EmployeeNames
(@format nvarchar(9))
RETURNS @tbl_Employees TABLE
(EmployeeID int PRIMARY KEY, [Employee Name] nvarchar(100))
AS
BEGIN
IF (@format = 'SHORTNAME')
INSERT @tbl_Employees
SELECT EmployeeID, LastName FROM HumanResources.vEmployee
ELSE IF (@format = 'LONGNAME')
INSERT @tbl_Employees
SELECT EmployeeID, (FirstName + ' ' + LastName)
FROM
HumanResources.vEmployee
RETURN
END
SELECT * FROM HumanResources.EmployeeNames('LONGNAME')
Practice: Creating Functions
In this practice, you will:
Create a scalar function
Create an inline table-valued function
Create a multi-statement
table-valued function
Drop user-defined functions
Lesson 2: Working with Functions
Guidelines for Creating Functions
Rewriting Stored Procedures as Functions
Deterministic and Nondeterministic Functions
Deterministic and Nondeterministic Functions
Deterministic functions
 Always returns the same value for the same set of
input values and database state
 Results can be indexed
 Aggregate and string built-in functions
Nondeterministic functions
 May return different results for the same set of input
values and database state
 Results cannot be indexed
 Configuration, cursor, metadata, security, system
statistics built-in functions
Guidelines for Creating Functions
 Determine function type
 Create one function for one task
 Create, test, and troubleshoot
 Qualify object names inside function
Consider ability of SQL Server 2005 to index function
 results
Rewriting Stored Procedures as Functions
Converting stored procedures to functions
For single resultset use a table-valued function
For single scalar value use a scalar function
Table-valued functions
Single SELECT statement with parameters
No update operations
No need for dynamic EXECUTE statements
Build intermediate results in to a temporary table
Lesson 3: Controlling Execution Context
What Is Execution Context?
The EXECUTE AS Clause
Options for Extending Impersonation Context
Demonstration: Controlling Execution Context
What Is Execution Context?
Ted
(No permissions)
Pat
(SELECT permission)
Sales.Order
(Owner: John)
GetOrders
Ted
(EXECUTE permission)
CREATE FUNCTION
AS
WITH
EXECUTE AS
RETURN ( SELECT
AS
RETURN ( SELECT
Function
(Owner: Pat)
Pat
GetOrders RETURNS TABLE
'Pat'
* FROM Sales.Order )
* FROM Sales.Order )
The EXECUTE AS Clause
EXECUTE AS options
The caller of the module
The person creating or altering the module
The owner of the module
A specified user
CREATE FUNCTION GetOrders RETURNS TABLE
SELF
WITH EXECUTE AS CALLER
OWNER
'Pat'
{
CALLER | SELF | OWNER | user_name }
AS
RETURN ( SELECT * FROM Sales.Order )
Options for Extending Impersonation Context
EXECUTE AS is restricted to current database by default
Establish a trust relationship to extend impersonation to
other databases
SET TRUSTWORTHY ON
GRANT AUTHENTICATE …
Mapped dbo
dbo
Signed Code Module
Certificate User
Demonstration: Controlling Execution Context
In this demonstration, you will see how to
use execution context within a stored
procedure
Lab: Implementing Functions
Exercise 1: Creating Functions
Exercise 2: Controlling Execution Context