Module 9: Implementing 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 • Can be invoked anywhere a scalar expression of the same data type is allowed 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 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-statementtable-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) Function (Owner: Pat) Pat CREATE FUNCTION GetOrders RETURNS TABLE AS RETURN ( SELECT * FROM Sales.Order ) CREATE FUNCTION GetOrders RETURNS TABLE WITH EXECUTE AS 'Pat' AS RETURN ( SELECT * 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 WITH EXECUTE AS SELF AS RETURN ( SELECT * FROM Sales.Order ) CREATE FUNCTION GetOrders RETURNS TABLE WITH EXECUTE AS CALLER AS RETURN ( SELECT * FROM Sales.Order ) CREATE FUNCTION GetOrders RETURNS TABLE WITH EXECUTE AS OWNER AS RETURN ( SELECT * FROM Sales.Order ) CREATE FUNCTION GetOrders RETURNS TABLE WITH EXECUTE AS 'Pat' AS RETURN ( SELECT * FROM Sales.Order ) CREATE FUNCTION GetOrders RETURNS TABLE WITH EXECUTE AS { 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 Certificate User Signed Code Module

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

Module 9: Implementing Functions