User-Defined Functions (UDF)

1. User-Defined Functions (UDF) User-Defined Functions

2. What is a User-Defined Function? • Can have parameters • Returns a value, either • A single scalar value • Unlike a stored procedure • Most data types are legal • A table • Can be called from a SELECT statement • Unlike stored procedures User-Defined Functions

3. Syntax and examples • Syntax CREATE FUNCTION someName(parameters) RETURNS someDataType BEGIN code RETURN variable | SELECT statement END • Example, returning a scalar/single value CREATE FUNCTION fnAvgGrade(@studentID int) RETURNS numeric(3,1) AS BEGIN return (select AVG(grade) from studentCourse where studentID = @studentID) END • Calling • SELECT name, dbo.fnAvgGrade(id) FROM student; User-Defined Functions

4. ExampleReturning a table • Definition CREATE FUNCTION fnStudentByName(@name varchar(100)) RETURNS TABLE AS RETURN (SELECT * FROM student WHERE name LIKE'%' + @name + '%'); • Calling • SELECT * FROM dbo.fnStudentByName('a'); User-Defined Functions

5. Examplereturning a scalar value CREATE FUNCTION [dbo].[numberOfEmployeesPrDepartment] ( @departmentIDint ) RETURNS int AS BEGIN -- Declare the return variable here DECLARE @result int -- Add the T-SQL statements to compute the return value here SELECT @result = COUNT(*) from teacher where departmentID = @departmentID -- Return the result of the function RETURN @result END User-Defined Functions

6. Using a scalar valued function in a CHECK constraint At most 6 teachers in each department: CREATE TABLE teacher ( teacherIDint IDENTITY(1,1) PRIMARY KEY, …. departmentIDint, CHECK (dbo. numberOfEmployeesPrDepartment( departmentID) <= 6) ) User-Defined Functions