1 / 6

User-Defined Functions (UDF)

User-Defined Functions (UDF). 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. Syntax and examples. Syntax

gomer
Télécharger la présentation

User-Defined Functions (UDF)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related