Learning Unit 8 UserDefined Functions in Microsoft SQL

Learning Unit 8 User-Defined Functions in Microsoft SQL Server 2005 Mount Vernon Data Systems LLC dedicated to keeping your SQL Server-powered business systems running

What is a User-Defined Function (UDF)? a mechanism for extending the functionality of the SQL Server a compiled subroutine that encapsulates code for re-use performs calculations and computations returns a value scalar (single value or NULL) table value (zero+ rows, 1+ columns) first introduced with SQL Server 2000 expanded functionality with each SQL Server release can be coded in T-SQL or CLR (. NET language of your choice) © Mount Vernon Data Systems LLC 2010 All Rights Reserved 2

Difference Between a UDF & a Stored Proc Stored procedure is explicitly called exec usp_My. Stored. Procedure it can modify data or database objects UDF is built into the SQL Server environment can be incorporated into queries, stored procedures, computed columns, constraints can return a value while changing nothing else in the database • • cannot modify data in tables cannot change database state cannot create or access temp tables (#table, ##table) cannot modify the database or any external resource (file) no error-checking in a UDF • • an error will cause execution of the UDF to stop which, in turn, causes the statement which invoked the UDF to be cancelled © Mount Vernon Data Systems LLC 2010 All Rights Reserved 3

UDF Life Cycle Create UDF scalar UDFs table-valued UDFs Alter UDF can modify existing UDF without dropping & recreating • • retains security permissions and settings will not affect any dependent functions, stored procedures, or triggers Drop UDF will fail if… • • • there are indexed computed columns that reference the function there are functions or views created with SCHEMABINDING that reference the function there are check or default constraints that reference the function © Mount Vernon Data Systems LLC 2010 All Rights Reserved 4

Where Are UDFs Stored in the Database? USE Adventure. Works GO -- display information about the UDFs in Adventure. Works -select * from sys. objects where type IN ('AF', 'FN', 'FS', 'FT', 'IF', 'TA', 'TF') order by name; --- display the parameters associated with each of the UDFs. . . -select o. name AS Function. Name, p. * from sys. objects AS o JOIN sys. parameters as p ON o. object_id = p. object_ID where type IN ('AF', 'FN', 'FS', 'FT', 'IF', 'TA', 'TF') order by o. name; --- display the UDF definition. . . select object_definition(object_id('dbo. ufn. Get. Accounting. End. Date')); select * from syscomments where ID in (select object_id('dbo. ufn. Get. Accounting. End. Date')) © Mount Vernon Data Systems LLC 2010 All Rights Reserved 5

Where Are UDFs Stored in the Database? USE Adventure. Works GO -- display information about the UDFs in Adventure. Works -select * from sys. objects where type IN ('AF', 'FN', 'FS', 'FT', 'IF', 'TA', 'TF') order by name; --- display the parameters associated with each of the UDFs. . . -select o. name AS Function. Name, p. * from sys. objects AS o JOIN sys. parameters as p ON o. object_id = p. object_ID where type IN ('AF', 'FN', 'FS', 'FT', 'IF', 'TA', 'TF') order by o. name; --- display the UDF definition. . . select object_definition(object_id('dbo. ufn. Get. Accounting. End. Date')); select * from syscomments where ID in (select object_id('dbo. ufn. Get. Accounting. End. Date')) © Mount Vernon Data Systems LLC 2010 All Rights Reserved 6

Types of UDFs select * from sys. objects where type IN ('AF', 'FN', 'FS', 'FT', 'IF', 'TA', 'TF') order by name; AF = Aggregate function (CLR) FN = SQL scalar function, FS = Assembly (CLR) scalar function FT = Assembly (CLR) table-valued function IF = SQL inline table-valued function TA = Assembly (CLR) DML trigger TF = SQL table-valued-function © Mount Vernon Data Systems LLC 2010 All Rights Reserved 7

UDF Operations CREATE FUNCTION owner_name. function_name (parameter_name parameter_data_type, etc. ) RETURNS return_data_type WITH UDF_OPTIONS AS BEGIN RETURN clause with computation instructions END GO ALTER FUNCTION owner_name. function_name (parameter_name parameter_data_type, etc. ) … GO DROP FUNCTION owner_name. function_name GO © Mount Vernon Data Systems LLC 2010 All Rights Reserved 8

UDF Options BEST PRACTICE encryption cannot read UDF code in the database catalog schemabinding prevents anyone from dropping underlying objects or making schema changes to referenced columns in the UDF execute as defines the security context of the function as it executes returns NULL on NULL input do not invoke the function if a parameter is NULL (result will always be NULL ANSI standard behaviour) improves performance by bypassing UDF execution • eliminates the need to check for NULL input in the UDF called on NULL input (default behaviour) invoke the function even when one of the input parms is NULL © Mount Vernon Data Systems LLC 2010 All Rights Reserved 9

Scalar UDFs return a single (scalar) value or NULL specify where ever a scalar expression is allowed query, constraint, computed column, inside stored proc, etc. must have BEGIN/END block defining the body of the UDF must provide values for all parameters, even optional ones specify the keyword DEFAULT T-SQL scalar UDFs are typically faster than CLR scalar UDFs consistent with SQL Server’s set-based optimization if exists (select 1 from sysobjects where id = object_id('dbo. udf_F 2 C') and type IN ('AF', 'FN', 'FS', 'FT', 'IF', 'TA', 'TF')) drop function dbo. udf_F 2 C GO © Mount Vernon Data Systems LLC 2010 All Rights Reserved 10

Create Scalar UDF, Grant Permission to Execute CREATE FUNCTION dbo. udf_F 2 C (@var. F int) RETURNS int WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT AS BEGIN return(@var. F-32)*5/9 END GO -- grant permissions to public group to use the UDF GRANT execute ON dbo. udf_F 2 C to public GO © Mount Vernon Data Systems LLC 2010 All Rights Reserved 11

Execute Scalar UDF -- execute the UDF; first declare and set a value for temperature Fahrenheit declare @var. Temp int set @var. Temp = 32 SELECT @var. Temp AS 'Fahrenheit', dbo. udf_F 2 C(@var. Temp) AS 'Celsius'; declare @var. Temp int set @var. Temp = 212 SELECT @var. Temp AS 'Fahrenheit', dbo. udf_F 2 C(@var. Temp) AS 'Celsius'; declare @var. Temp int set @var. Temp = NULL SELECT @var. Temp AS 'Fahrenheit', dbo. udf_F 2 C(@var. Temp) AS 'Celsius'; © Mount Vernon Data Systems LLC 2010 All Rights Reserved 12

Create Scalar UDF, Multiple Inputs, Cast Output Calculate profit margin as a percent Multiple inputs, cast output as different data type CREATE FUNCTION dbo. udf_Profit. Margin (@var. Wholesale. Price money, @var. Retail. Price money) RETURNS decimal(7, 2) WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT AS BEGIN DECLARE @var. Result decimal(7, 2) SET @var. Result = cast((@var. Retail. Price/@var. Wholesale. Price)* 100 AS decimal(7, 2)) RETURN @var. Result END GO GRANT execute ON dbo. udf_Profit. Margin to public GO © Mount Vernon Data Systems LLC 2010 All Rights Reserved 13

Execute Scalar UDF, Multiple Inputs, Cast Output declare @var. Wholesale. Price money declare @var. Retail. Price money set @var. Wholesale. Price = 25. 00 set @var. Retail. Price = 50. 00 SELECT @var. Wholesale. Price AS 'Wholesale Price', @var. Retail. Price AS 'Retail Price', dbo. udf_Profit. Margin (25. 00, 50. 00) AS '% Profit Margin'; declare @var. Wholesale. Price money declare @var. Retail. Price money set @var. Wholesale. Price = 5. 00 set @var. Retail. Price = 50. 00 SELECT @var. Wholesale. Price AS 'Wholesale Price', @var. Retail. Price AS 'Retail Price', dbo. udf_Profit. Margin (5. 00, 50. 00) AS '% Profit Margin'; © Mount Vernon Data Systems LLC 2010 All Rights Reserved 14

Scalar UDF, Multiple Inputs, Procedural Logic Categorize authors by region return table of results CREATE FUNCTION dbo. udf_Author. Region (@var. State char(2)) RETURNS varchar(10) WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT AS BEGIN declare @Var. Result varchar(10) IF (@var. State = 'CA' OR @var. State = 'OR' OR @var. State = 'UT') SET @Var. Result = 'Western' ELSE IF @var. State = 'TN' SET @Var. Result = 'Southern' ELSE IF (@var. State = 'KS' OR @var. State = 'MI' OR @var. State = 'IN') SET @Var. Result = 'Midwestern' ELSE set @Var. Result = 'unknown' RETURN @Var. Result END GO © Mount Vernon Data Systems LLC 2010 All Rights Reserved 15

Execute Scalar UDF, Mult. Inputs, Procedural Logic SELECT au_lname + ', ' + au_fname AS "Full Name", dbo. udf_Author. Region (state) AS "Region" FROM dbo. authors WHERE state = 'MI'; SELECT au_lname + ', ' + au_fname AS "Full Name", dbo. udf_Author. Region (state) AS "Region" FROM dbo. authors WHERE state = ‘CA'; SELECT au_lname + ', ' + au_fname AS "Full Name", dbo. udf_Author. Region (city) AS "Region" FROM dbo. authors WHERE state = ‘CA’; © Mount Vernon Data Systems LLC 2010 All Rights Reserved 16

Execute Scalar UDF, Mult. Inputs, Procedural Logic SELECT au_lname + ', ' + au_fname AS "Full Name", dbo. udf_Author. Region (state) AS "Region" FROM dbo. authors WHERE state = 'MI'; SELECT au_lname + ', ' + au_fname AS "Full Name", dbo. udf_Author. Region (state) AS "Region" FROM dbo. authors WHERE state = ‘CA'; SELECT au_lname + ', ' + au_fname AS "Full Name", dbo. udf_Author. Region (city) AS "Region" FROM dbo. authors WHERE state = ‘CA’; © Mount Vernon Data Systems LLC 2010 All Rights Reserved 17

Create Scalar UDF, Optional Parameters Extract the first “n” bytes of a string CREATE FUNCTION dbo. udf_Extractor (@var. String varchar(80), @var. Extract int = 1) RETURNS varchar(80) WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT AS BEGIN set @var. String = substring(@var. String, 1, @var. Extract) return @var. String END GO © Mount Vernon Data Systems LLC 2010 All Rights Reserved 18

Execute Scalar UDF, Mult. Inputs, Procedural Logic select title, dbo. udf_Extractor(title, 5) from dbo. titles; select title, dbo. udf_Extractor(title, default) from dbo. titles; © Mount Vernon Data Systems LLC 2010 All Rights Reserved 19

Table-valued UDFs return a table typically specified in the FROM clause of a SELECT statement Inline table-valued UDFs similar to a view in that the result set is defined by a query can accept input parameters (“parameterized view”) Per-row UDFs SQL Server 2005, allows non-deterministic functions • getdate(), rand() • specified in the SELECT clause Multi-statement table-valued UDFs returns a table variable function routine cannot be expressed as a single query © Mount Vernon Data Systems LLC 2010 All Rights Reserved 20

Create Inline Table-valued UDF return all authors who live in a state… input 2 -letter state code No Begin/End Block CREATE FUNCTION dbo. udf_Authors. From. AState (@var. State char(2)) RETURNS TABLE WITH SCHEMABINDING AS RETURN (SELECT au_fname, au_lname, city, state, zip FROM dbo. authors WHERE state = @var. State) GO SQL Server query processor replaces an Inline UDF reference with its definition & generates an execution plan based on access to the underlying database objects, just like it treats a view. © Mount Vernon Data Systems LLC 2010 All Rights Reserved 21

Execute Inline Table-valued UDF select * from dbo. udf_Authors. From. AState('MI'); select * from dbo. udf_Authors. From. AState('CA'); © Mount Vernon Data Systems LLC 2010 All Rights Reserved 22

Create Inline Table-valued UDF with Aggregation return the total number of orders for any store input 4 -character store ID CREATE FUNCTION dbo. udf_Sales. By. Store (@var. Store. ID char(4)) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT s 2. stor_id, s 1. stor_name, SUM(s 2. qty) AS 'Order Total' FROM dbo. Stores s 1 JOIN dbo. Sales s 2 ON s 1. stor_id = s 2. stor_id WHERE s 2. stor_id = @var. Store. ID GROUP BY s 2. stor_id, s 1. stor_name ) GO © Mount Vernon Data Systems LLC 2010 All Rights Reserved 23

Execute Inline Table-valued UDF with Aggregation select * from dbo. udf_Sales. By. Store(6380); select * from dbo. udf_Sales. By. Store(7131); select * from dbo. udf_Sales. By. Store(8042); © Mount Vernon Data Systems LLC 2010 All Rights Reserved 24

Create Per-row Table-valued UDF with Get. Date() non-deterministic functions allowed with SQL Server 2005 determined once, returned for each row in the entire result set CREATE FUNCTION dbo. udf_Get. Date() RETURNS datetime WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT AS BEGIN RETURN getdate(); END GO -- grant permissions to groups to use the UDF GRANT execute ON dbo. udf_Get. Date to public GO © Mount Vernon Data Systems LLC 2010 All Rights Reserved 25

Execute Per-row Table-valued UDF with Get. Date() SELECT ord_num, dbo. udf_Get. Date() AS 'Current Date & Time' FROM dbo. Sales; © Mount Vernon Data Systems LLC 2010 All Rights Reserved 26

Create Per-row Table-valued UDF with Day() deterministic functions resolved for each row different value, depending on input CREATE FUNCTION dbo. udf_day(@var. Date datetime) RETURNS int WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT AS BEGIN RETURN day(@var. Date); END GO -- grant permissions to groups to use the UDF GRANT execute ON dbo. udf_day to public GO © Mount Vernon Data Systems LLC 2010 All Rights Reserved 27

Execute Per-row Table-valued UDF with Day() SELECT ord_num, ord_date, dbo. udf_day(ord_date) AS 'Day Number' FROM dbo. Sales; © Mount Vernon Data Systems LLC 2010 All Rights Reserved 28

Create Multi-Statement Table-valued UDF create a reporting hierarchy layout… list all employees who report to the individual specified USE Adventure. Works GO CREATE FUNCTION dbo. udf_Reporting. Hierarchy (@In. Emp. ID INTEGER) RETURNS @var. Rep. Hierarchy TABLE ( Employee. ID int primary key NOT NULL, Name nvarchar(255) NOT NULL, Title nvarchar(50) NOT NULL, Employee. Level int NOT NULL, Sort nvarchar (255) NOT NULL ) WITH SCHEMABINDING AS © Mount Vernon Data Systems LLC 2010 All Rights Reserved 29

Create Multi-Statement Table-valued UDF BEGIN WITH Rep. Hierarchy(Name, Title, Employee. ID, Employee. Level, Sort) AS (SELECT convert(varchar(255), c. First. Name + ' ' + c. Last. Name), e. Title, e. Employee. ID, 1, convert(varchar(255), c. First. Name + ' ' + c. Last. Name) FROM Human. Resources. Employee AS e JOIN Person. Contact AS c ON e. Contact. ID = c. Contact. ID WHERE e. Employee. ID = @In. Emp. ID UNION ALL SELECT convert(varchar(255), replicate ('| ' , Employee. Level)+c. First. Name + ' ' + c. Last. Name), e. Title, e. Employee. ID, Employee. Level + 1, convert (Varchar(255), rtrim(sort) + '| ' + First. Name + ' ' + Last. Name) FROM Human. Resources. Employee as e JOIN Person. Contact AS c ON e. Contact. ID = c. Contact. ID JOIN Rep. Hierarchy AS d ON e. Manager. ID = d. Employee. ID ) © Mount Vernon Data Systems LLC 2010 All Rights Reserved 30

Create Multi-Statement Table-valued UDF -- second query -- copy the generated columns to the result of the function INSERT @var. Rep. Hierarchy SELECT Employee. ID, Name, Title, Employee. Level, Sort FROM Rep. Hierarchy RETURN END GO © Mount Vernon Data Systems LLC 2010 All Rights Reserved 31

Execute Multi-Statement Table-valued UDF with Day() SELECT Employee. ID, Name, Title, Employee. Level FROM dbo. udf_Reporting. Hierarchy(109) ORDER BY Sort; © Mount Vernon Data Systems LLC 2010 All Rights Reserved 32

Execute Multi-Statement Table-valued UDF with Day() SELECT Employee. ID, Name, Title, Employee. Level FROM dbo. udf_Reporting. Hierarchy(109) ORDER BY Sort; SELECT Employee. ID, Name, Title, Employee. Level FROM dbo. udf_Reporting. Hierarchy(273) ORDER BY Sort; © Mount Vernon Data Systems LLC 2010 All Rights Reserved 33

Execute Multi-Statement Table-valued UDF with Day() SELECT Employee. ID, Name, Title, Employee. Level FROM dbo. udf_Reporting. Hierarchy(109) ORDER BY Sort; SELECT Employee. ID, Name, Title, Employee. Level FROM dbo. udf_Reporting. Hierarchy(273) ORDER BY Sort; SELECT Employee. ID, Name, Title, Employee. Level FROM dbo. udf_Reporting. Hierarchy(284) ORDER BY Sort; © Mount Vernon Data Systems LLC 2010 All Rights Reserved 34

Reasons To Use a UDF modular programming write once, use many reusable code, can be modified independently of calling program faster execution pre-compiled, caches the execution plan & reuses for repeated executions reduce network traffic filter data based on some complex constraint that is best expressed via UDF invoke UDF in the WHERE clause © Mount Vernon Data Systems LLC 2010 All Rights Reserved 35

The Author… Michelle A. Poolet, MCIS, University of Denver Zachman Certified™ - Enterprise Architect Contributing Editor, SQL Server Magazine Adjunct faculty, University of Denver, University College President/co-founder of Mount Vernon Data Systems – database consulting company specializing in database systems, data modeling, courseware development, education/training. Michelle@Mount. Vernon. Data. Systems. com Hanging out near the beach at Kailua. Kona, Hawaii…aloha, everyone! © Mount Vernon Data Systems LLC 2010 All Rights Reserved 36